Laravel Query with subquery not working

Question

Maybe I am doing this incorrectly, however what I want to do logically is this:

I want to first select all posts that contain a certain string.

Then from those results, I want to narrow it down to only posts within a certain date (Y|m|d).

So this is what im doing at the moment:

$results = DB::table('posts');

if ($query == null){
  $query = "Posted On: " . $date;
}else {
  $results->where('posts.title', 'LIKE', "%$query%")
          ->orWhere('posts.meta_description', 'LIKE', "%$query%")
          ->orWhere('posts.meta_keywords', 'LIKE', "%$query%")
          ->orWhere('posts.body', 'LIKE', "%$query%")
          ->orWhere('posts.seo_title', 'LIKE', "%$query%");
  $query = $query." / Posted On: " . $date;
}


$results->where(function($query) use ($date){

  $date = explode('-', $date);
  if (!isset($date[1])){
    $query->whereYear('created_at', '=', $date[0]);
  }else if(isset($date[1]) && !isset($date[2])){
    $query->whereYear('created_at', '=', $date[0])
          ->whereMonth('created_at', '=', $date[1]);
  }else if (isset($date[1]) && isset($date[2])){
    $query->whereYear('created_at', '=', $date[0])
          ->whereMonth('created_at', '=', $date[1])
          ->whereDay('created_at', '=', $date[2]);
  }
});

$results = $results->paginate($this->paginate_size());

However, this query is still just returning anything that contains the query string and ignoring the fact that I want to select a subset that are only posts on a specific year/month/day.

What am I missing?

Edit:

Using $results->toSql(); I get the query:

select * from `posts` where `posts`.`title` LIKE ? or 
`posts`.`meta_description` LIKE ? or `posts`.`meta_keywords` LIKE ? or 
`posts`.`body` LIKE ? or `posts`.`seo_title` LIKE ? and year(`created_at`) = ?

However, it is not working...

If I enter for example the query "Lorem" it will find all of the posts with the text Lorem in it.

However, when I add a specific date like just "2015", it should not show ANY posts from 2015 with the query Lorem since it does not exist.

So there is some logic problem here. Any ideas?

can you please display the query that laravel execute (using ->toSql() ) — m.benslimane
– m.benslimane, Commented Dec 23, 2016 at 20:14
You should set $results to the value returned by the where function. So $results = $results->where(... — theduck
– theduck, Commented Dec 23, 2016 at 20:14
The query I get is : select * from posts` where posts.title LIKE ? or posts.meta_description LIKE ? or posts.meta_keywords LIKE ? or posts.body LIKE ? or posts.seo_title LIKE ? and year(created_at) = ? ` — Borovez
– Borovez, Commented Dec 28, 2016 at 14:54
The problem is that the query completely ignores the "Date" - so i I search for "Lorem" it will find 2 posts with the text Lorem, but if I specify the date being 2015, it should find 0 posts since there are no posts in 2015 with the text "lorem" — Borovez
– Borovez, Commented Dec 28, 2016 at 14:59

meda · Accepted Answer · 2016-12-23 21:41:45Z

2

You shouldn't need a subquery.

If you have the proper query in place you should be able to access in the same query.

Of course you can have the dynamic where with the conditions, but do it on the same query. Something along the lines, for example:

if ($query == null){
  $query = "Posted On: " . $date;
}

$results->where('posts.title', 'LIKE', "%$query%")
        ->orWhere('posts.meta_description', 'LIKE', "%$query%")
        ->orWhere('posts.meta_keywords', 'LIKE', "%$query%")
        ->orWhere('posts.body', 'LIKE', "%$query%")
        ->orWhere('posts.seo_title', 'LIKE', "%$query%");

$date = explode('-', $date);
if (!isset($date[1])){
  $results->whereYear('created_at', '=', $date[0]);
}else if(isset($date[1]) && !isset($date[2])){
  $results->whereYear('created_at', '=', $date[0])
          ->whereMonth('created_at', '=', $date[1]);
}else if (isset($date[1]) && isset($date[2])){
  $results->whereYear('created_at', '=', $date[0])
          ->whereMonth('created_at', '=', $date[1])
          ->whereDay('created_at', '=', $date[2]);
}

$results = $results->paginate($this->paginate_size());

answered Dec 23, 2016 at 21:41

meda

45.5k14 gold badges97 silver badges123 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Borovez Over a year ago

I have actually tried this before, and it has the same results... the problem is that the query completely ignores the "Date" - so i I search for "Lorem" it will find 2 posts with the text Lorem, but if I specify the date being 2015, it should find 0 posts since there are no posts in 2015 with the text "lorem"

Borovez Over a year ago

I updated my original question to include the SQL... which is: select * from posts where posts.title LIKE ? or posts.meta_description LIKE ? or posts.meta_keywords LIKE ? or posts.body LIKE ? or posts.seo_title LIKE ? and year(created_at) = ?

meda Over a year ago

Have you looked at what inside of $date[0] does it work when you run this query manually. this sql looks fine

user6612690 · Accepted Answer · 2016-12-23 21:41:20Z

1

You can use the whereDate() Eloquent method:

$results = DB::table('posts');
if ($query == null){
  $query = "Posted On: " . $date;
}else {
  $results->where(function($q) {
        $q->where('posts.title', 'LIKE', "%$query%")
          ->orWhere('posts.meta_description', 'LIKE', "%$query%")
          ->orWhere('posts.meta_keywords', 'LIKE', "%$query%")
          ->orWhere('posts.body', 'LIKE', "%$query%")
          ->orWhere('posts.seo_title', 'LIKE', "%$query%");
  })->whereDate('created_at', '=', date('Y-m-d');
  $query = $query." / Posted On: " . $date;
}
$results = $results->paginate($this->paginate_size());

answered Dec 23, 2016 at 21:41

user6612690

Collectives™ on Stack Overflow

Laravel Query with subquery not working

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related