Laravel where exists query issue / not working

Question

Alright so I have a table of items where duplicate items can exist with the same 'parent_uuid' that can be 'published' or 'discarded'. I'm trying to get a 'discarded' group of items, where published is 0 but where there is not a duplicate row with the same 'parent_uuid' that has published marked 1.

When I write the query in sql, it behaves correctly:

SELECT * FROM `items` AS `items` WHERE `published` = 0 AND `created_at` < '2015-08-30 17:23:29' AND NOT EXISTS (SELECT 1 FROM `items` AS `check` WHERE `check`.`parent_uuid` = `items`.`parent_uuid` AND `published` = 1) GROUP BY `parent_uuid`

My test cases return as I expect them to be. When I write this in Laravel query builder like this:

  $discarded = self::from('items as items')
    ->where('published', 0)
    ->where('created_at', '<', Carbon::now()->subMonth()->toDateTimeString())
    ->whereNotExists(function($query) {
      $query->select(DB::raw(1))
        ->from('items as check')
        ->where('check.parent_uuid', 'items.parent_uuid')
        ->where('published', 1);
    })
    ->groupBy('parent_uuid');

I get more results than I'm supposed to. I get results for items that should fail the where not exists subquery. When I output the last query, it all looks fine. And if I copy this query into my sql client and swap in the variables, it works as I expect it to.

array (size=3)
  'query' => string 'select * from `items` as `items` where `published` = ? and `created_at` < ? and not exists (select 1 from `items` as `published` where `published`.`parent_uuid` = ? and `published` = ?) group by `parent_uuid`' (length=211)
  'bindings' => 
    array (size=4)
      0 => int 0
      1 => string '2015-08-30 17:23:29' (length=19)
      2 => string 'items.parent_uuid' (length=22)
      3 => int 1
  'time' => float 442.8

I'm unsure what I'm doing wrong and I've tried just about everything I can think of.

Does anyone know what might be going on here? I'm on Laravel 4.2.

Throttlehead · Accepted Answer · 2015-10-01 15:40:45Z

The problem seems to be with Laravel's query builder and is probably database driver related. It could be a temporary issue with the build of the library I currently have.

A solution I found for these situations is to create the raw database string and pipe it in to the advanced where select query. You could do something like this:

  $where_not_exists = '1 FROM `items` AS `check` WHERE `check`.`parent_uuid` = `items`.`parent_uuid` AND `published` = 1';

  $discarded = Items::from('items as items')
    ->where('published', 0)
    ->where('created_at', '<', Carbon::now()->subMonth()->toDateTimeString())
    ->whereNotExists(function($query) use ($where_not_exists) {
      $query->select(DB::raw($where_not_exists));
    })
    ->groupBy('parent_uuid');

  if ($count === TRUE) {
    return $discarded->count();
  }

  return $discarded->get();

It's not terribly unreadable. Just be sure to never inject user sent input directly into these!

Collectives™ on Stack Overflow

Laravel where exists query issue / not working

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related