Perform advanced where clause Eloquent

Question

I would know how to perform an 'Advanced Where'. I didn't find anything in the documentation that explain what I want.. even there. (cf: http://laravel.com/docs/4.2/queries#advanced-wheres).

Post::whereHas('international_post_en', function($q) {
       $q->where('is_published', 1);
  })->whereHas('categories', function($q) {
       $q->where('name', 'test-one');
  })->orWhereHas('subcategories', function($q) {
       $q->where('name', 'test-two');
  })->with('categories', 'subtags')
    ->get();

My query look like this:

select * from `posts` where `posts`.`deleted_at` is null and (select count(*) from `international_posts_en` where `international_posts_en`.`posts_id` = `posts`.`id` and `is_published` = ?) >= 1 and (select count(*) from `categories` inner join `posts_has_categories` on `categories`.`id` = `posts_has_categories`.`categories_id` where `posts_has_categories`.`posts_id` = `posts`.`id` and `name` = ?) >= 1 or (select count(*) from `subcategories` inner join `posts_has_subcategories` on `subcategories`.`id` = `posts_has_subcategories`.`subcategories_id` where `posts_has_subcategories`.`posts_id` = `posts`.`id` and `name_en` = ?) >= 1

But I want my query to look like this:

select * from `posts` where `posts`.`deleted_at` is null and (select count(*) from `international_posts_en` where `international_posts_en`.`posts_id` = `posts`.`id` and `is_published` = ?) >= 1 and [(](select count(*) from `categories` inner join `posts_has_categories` on `categories`.`id` = `posts_has_categories`.`categories_id` where `posts_has_categories`.`posts_id` = `posts`.`id` and `name` = ?) >= 1 or (select count(*) from `subcategories` inner join `posts_has_subcategories` on `subcategories`.`id` = `posts_has_subcategories`.`subcategories_id` where `posts_has_subcategories`.`posts_id` = `posts`.`id` and `name_en` = ?) >= 1[)]

(Sorry It is not very readable)

You can see the changes within the brackets. So, I want the whereHas and the orWhereHas clause to be grouped within parenthesis.

Is it possible with the Laravel Query Builder or should I make a handmade query?

Thank you in advance.

score 1 · Accepted Answer · answered Nov 12 '14 at 20:42

1

Post::whereHas('international_post_en', function($q) {
       $q->where('is_published', 1);
  })->where(function ($q) {
     $q->whereHas('categories', function($q) {
       $q->where('name', 'test-one');
     })->orWhereHas('subcategories', function($q) {
       $q->where('name', 'test-two');
     });
  })->with('categories', 'subtags')->get();

In fact this is the very first example on the page you linked. However that example is pretty inaccurate, since you wouldn't group and wheres against or where, but the other way around..

answered Nov 12 '14 at 20:42

Jarek Tkaczyk

78,987
25
159
157

Ok sorry I should have tryied this. I was sure it could not work if it was on relationship. Thank you it works like I want. – KeizerBridge Nov 13 '14 at 09:11
Just to avoid another post, do you know if i can apply the first() method on an eager loading with a constraint? – KeizerBridge Nov 13 '14 at 12:56
You mean `first` instead of `get` for the whole query? Of course you can. – Jarek Tkaczyk Nov 13 '14 at 12:59
Nope, only on the with method. In my exemple I want to get only the first 'subtags' and the with method return a multidimensional array. So i would apply the first() method only for the this case not for the whole query – KeizerBridge Nov 13 '14 at 13:12
No, it won't work and will lead to unexpected results. Read this http://stackoverflow.com/a/24350807/784588 – Jarek Tkaczyk Nov 13 '14 at 13:37

Perform advanced where clause Eloquent

1 Answers1