Comparing two withCount results - different outcomes for Sqlite and MySQL

Hey there,

I've been developing a project using Sqlite for the beginning phase, and then switched to mysql, and now one of my main queries doesn't work and throws Column not found: 1054 Unknown column 'matching_count' in 'where clause'...

Here's my query scope:

public function scopeMatching($query, $options)
    {
        return $query->whereHas('combinations', function ($c) use ($options) {
            return $c->withCount([
                'options as matching' => function ($o) use ($options) {
                    return $o->whereIn('id', $options);
                }
            ])->withCount('options as total')
                ->whereColumn('matching_count', '=', 'total_count');
        });
    }

What I'm trying to do is find all combination options that are in $options array, and compare their count with all combination options. And then only return those combinations where these two counts are equal.

This is the SQL query I get when using Sqlite:

select * from "products" where exists (select *, (select count(*) from "quiz_options" inner join "product_combination_options" on "quiz_options"."id" = "product_combination_options"."option_id" where "product_combinations"."id" = "product_combination_options"."combination_id" and "id" in (?, ?, ?)) as "matching_count", (select count(*) from "quiz_options" inner join "product_combination_options" on "quiz_options"."id" = "product_combination_options"."option_id" where "product_combinations"."id" = "product_combination_options"."combination_id") as "total_count" from "product_combinations" where "products"."id" = "product_combinations"."product_id" and "matching_count" = "total_count")

This is the SQL query I get when using MySql:

select * from `products` where exists (select *, (select count(*) from `quiz_options` inner join `product_combination_options` on `quiz_options`.`id` = `product_combination_options`.`option_id` where `product_combinations`.`id` = `product_combination_options`.`combination_id` and `id` in (?, ?, ?)) as `matching_count`, (select count(*) from `quiz_options` inner join `product_combination_options` on `quiz_options`.`id` = `product_combination_options`.`option_id` where `product_combinations`.`id` = `product_combination_options`.`combination_id`) as `total_count` from `product_combinations` where `products`.`id` = `product_combinations`.`product_id` and `matching_count` = `total_count`)

First one works, second one doesn't. What am I missing?

I appreciate any help :)

maslauskast

9 years ago

Level 8

I don't know if I expressed the issue clearly, so I'll try again.

I have products that have specific combinations when they should be assigned to a client. I find the correct product by checking all combination options against themselves - first, I get the number of matching options which I pass via the $options array, and then I check if that number is equal to all options for that particular combination. If these numbers match, then combination matches, therefore product matches.

What I'm trying to do is compare relation count twice - once that matches subquery, and once with total. I got it to work by passing a subquery that I copied from the final query, but I want to get it to work using Laravel's query builder.

Here's what I got going right now:

public function scopeMatching($query, $options)
    {

return $query->whereHas('combinations', function ($combos) use ($options) {

            $matchingQuery = "select count(*) from `quiz_options` 
inner join `product_combination_options` on `quiz_options`.`id` = `product_combination_options`.`option_id` 
where `product_combinations`.`id` = `product_combination_options`.`combination_id` 
and `id` in ({$options->implode(',')})";

            $totalQuery = "select count(*) from `quiz_options`
inner join `product_combination_options` on `quiz_options`.`id` = `product_combination_options`.`option_id`
where `product_combinations`.`id` = `product_combination_options`.`combination_id`";


            return $combos->whereRaw("({$matchingQuery}) = ({$totalQuery})");

    });
}

Notice that $matchingQuery is exactly the same as $totalQuery, it only has additional "and id in..."

Please or to participate in this conversation.