在Laravel 5中有多个内部连接和过滤器。

I have the following models and relations: Ads has many-to-many with Cities Ads has many-to-many with Categories

我有以下的模型和关系:广告多对多，城市广告有多对多的类别。

Now I want to get Ads for a specific City and specific Category. So in SQL I do the following and getting the correct result:

现在我想为一个特定的城市和特定的类别做广告。在SQL中，我做了如下操作，得到了正确的结果:

select * from ads
inner join ad_cities on ad_cities.adId = ads.id
inner join ad_categories on ad_categories.adId = ads.id
where ad_cities.cityId = 1525
and ad_categories.categoryId = 6

I know it can be done using DB:table(xxxx)->join->.... But I want to know if the same can be done on the model without taking the DB:table approach.

我知道这是可以做到使用DB:表(xxxx)- >连接- > ....但是我想知道，如果不使用DB:table方法，是否可以在模型上执行相同的操作。

I tried the following and getting the result, but the query generated doesn't seem to be efficient.

我尝试了下面的方法并得到了结果，但是生成的查询似乎没有效率。

return Category::find(6)
            ->ads()
            ->whereHas('cities', function ($query) {
                    $query->where('cityId', '=', '1525');
                    })                    
            ->where('statusName', '=', 'PUBLISHED')
            ->orderBy('publishedAt', 'DESC')
            ->simplePaginate($pageSize);

This generated the following query:

这产生了以下查询:

select `ads`.*, `ad_categories`.`categoryId` as `pivot_categoryId`, `ad_categories`.`adId` as `pivot_adId` from `ads` inner join `ad_categories` on `ads`.`id` = `ad_categories`.`adId` where `ad_categories`.`categoryId` = '6' and (select count(*) from `cities` inner join `ad_cities` on `cities`.`id` = `ad_cities`.`cityId` where `ad_cities`.`adId` = `ads`.`id` and `cityId` = '1525') >= 1 and `statusName` = 'PUBLISHED' order by `publishedAt` desc limit 16 offset 0

Appreciate your help. Thanks, Velu

感谢你的帮助。谢谢,礁泻湖

1 个解决方案

#1

I've spent quite some time banging my head against this one in my own projects, and the best I can some up with is this:

我花了很长时间在自己的项目中对这一问题进行抨击，我能做的最好的事情就是:

return Category::find(6)
            ->ads()
            ->join('ad_cities', 'ad_cities.adId', '=', 'ads.id')
            ->where('ad_cities.cityId', 1525)                   
            ->where('statusName', '=', 'PUBLISHED')
            ->orderBy('publishedAt', 'DESC')
            ->simplePaginate($pageSize);

It's something, right? It gives you the right SQL and preserves the Eloquent API to some extent. The other thing I've been doing, with queries that I couldn't even shoehorn into this, is using hydrate and hydrateRaw after fetching with raw SQL.

这是什么,对吧?它为您提供了正确的SQL，并在一定程度上保留了雄辩的API。我一直在做的另一件事，我甚至不能把它塞进这个问题，就是在用原始的SQL抓取之后，使用水合物和hydrateRaw。

This way, when someday I figure out how to use Eloquent effectively and/or Eloquent matures, the interface between Model and Controller will still be right and I can just replace the SQL with ORM goodness.

这样，当有一天我知道如何有效地使用有说服力的和/或有说服力的成熟时，模型和控制器之间的接口仍然是正确的，我可以用ORM的优点来替换SQL。

Also, let me know if you find a better way.

还有，如果你找到更好的方法，请告诉我。

#1