Query Builder: Binding values to queries

hi guys, i wonder if you could shed some light. Not really sure if I did something wrong but here is the thing. Ive noticed this strange behavior few weeks ago but made a temporary solution for it and just moved on. Now, it seems that everytime i added a lot of WHERES and JOINS to my queries the issue became more obvious.

i.e.

//joining table with condition
$query->leftjoin('qka_promo_listing as promolistings', function($join) {
            $join->on('promolistings.listing_id', '=', 'sr.id')
                ->where('promolistings.status', '=', 'active');
        });

$query->where('sr.active', '=', 'active');
$query->where('sr.pause', '=', 0);

I get an error: Invalid parameter number (SQL: select _make_id.make_id as name, count(_make_id.make_id) as totalCount, ' dev2.quokka.com.au/search/western-australia/loc|st|16/0km/caravan-&-campervan/sc|6/make_id-{val} ' as url, make.Description as desc from (select mapped.airconditioning, body.body_description, mapped.body_type_id, mapped.drive_train, fuel.fuel_description, mapped.fuel_id, mapped.listing_type, mapped.make_id from qka_listings as sr left join qka_promo_listing as promolistings on promolistings.listing_id = sr.id and promolistings.status = 16 inner join qka_listing_item as qka_listing_item on qka_listing_item.listing_id = sr.id inner join qka_automotive as mapped on mapped.item_id = qka_listing_item.id left join qka_rb_body as body on body.id = mapped.body_type_id left join qka_rb_fuel as fuel on fuel.id = mapped.fuel_id where sr.state_id = 6 and sr.category_map_id = active and sr.active = 0 and sr.pause = active and (promolistings.promo_id is null or promolistings.status = Null) group by sr.id order by sr.date_updated desc) as _make_id inner join RBMake as make on make.MakeCode LIKE _make_id.make_id where (_make_id.make_id != and _make_id.make_id != ?) group by _make_id.make_id)

supposedly sr.active should be equal to active and sr.pause is 0

But if im going to comment the condition:

$query->leftjoin('qka_promo_listing as promolistings', function($join) {
            $join->on('promolistings.listing_id', '=', 'sr.id');
                //->where('promolistings.status', '=', 'active');
        });

The execution of the query works.

I'm also having the same trouble with queries with HAVING clause, specifically distance (haversine) search and full text (relevance) search. Binding values doesn't seem to be in place.

Ive read somewhere before that you are having issues with aggregation/pagination if sql contains HAVING clause Im not sure if this is somewhat related.

Thanks All!

jimmck

10 years ago

Level 13

Hi, This query dump would never run. Just this part alone.....

order by sr.date_updated desc) as _make_id inner join RBMake as make on make.MakeCode LIKE _make_id.make_id where (_make_id.make_id != and _make_id.make_id != ?) group by _make_id.make_id)

I do not use Eloquent, the class syntax is baffling! Sorry I love everything Laravel, but this. There is no way to generate the actual SQL that is run? I see so many questions for what should be simple SQL. I really humbly suggest, a posting rule for Show The SQL please. In SQL not some in between Eloquent/PHP speak. I got flamed once before for saying this, but one good SQL statement and its result set can save lots of crazy coding. Let the database engine do its work.

jlarisma

10 years ago

Level 1

select 
  `_make_id`.`make_id` as `name`, 
  count(_make_id.make_id) as totalCount, 
  ' dev2.quokka.com.au/search/western-australia/loc|st|16/0km/caravan-&-campervan/sc|6/make_id-{val} ' as url, `make`.`Description` as `desc` 
  from (
    select 
      `mapped`.`airconditioning`, 
      `body`.`body_description`, 
      `mapped`.`body_type_id`, 
      `mapped`.`drive_train`, 
      `fuel`.`fuel_description`, 
      `mapped`.`fuel_id`, 
      `mapped`.`listing_type`, 
      `mapped`.`make_id` 
    from 
      qka_listings as sr 
    left join 
      `qka_promo_listing` as `promolistings` on `promolistings`.`listing_id` = `sr`.`id` 
    inner join 
      `qka_listing_item` as `qka_listing_item` on `qka_listing_item`.`listing_id` = `sr`.`id` 
    inner join 
      `qka_automotive` as `mapped` on `mapped`.`item_id` = `qka_listing_item`.`id` 
    left join 
      `qka_rb_body` as `body` on `body`.`id` = `mapped`.`body_type_id` 
    left join 
      `qka_rb_fuel` as `fuel` on `fuel`.`id` = `mapped`.`fuel_id` 
    where 
      `sr`.`state_id` = 16 and `sr`.`category_map_id` = 6 and `sr`.`active` = 'active'
    and 
      `sr`.`pause` = 0 
    group by `sr`.`id` 
    order by `sr`.`date_updated` desc) as _make_id 
    inner join 
      `RBMake` as `make` on `make`.`MakeCode` = `_make_id`.`make_id`
    where (`_make_id`.`make_id` != Null or `_make_id`.`make_id` != '')
    group by `_make_id`.`make_id`

That is supposedly the converted SQL

which is should give an output name | totalCount | url | desc ----------|----------------- |------------------------------------------------------------------------------------|---------- ASIC | 1 | dev2.quokka.com.au/search/western-australia/loc|s... | Asia Motors

Apologize if its kinda messy.

jlarisma

10 years ago

Best Answer

Level 1

But as mentioned, if I added

$query->leftjoin('qka_promo_listing as promolistings', function($join) {
            $join->on('promolistings.listing_id', '=', 'sr.id');
                ->where('promolistings.status', '=', 'active'); //uncomment this
        });

The values on the query above are no longer at its respective places

select 
      `_make_id`.`make_id` as `name`, 
      count(_make_id.make_id) as totalCount, 
      ' dev2.quokka.com.au/search/western-australia/loc|st|16/0km/caravan-&-campervan/sc|6/make_id-{val} ' as url, 
      `make`.`Description` as `desc` 
     from 
     (
      select 
        `mapped`.`airconditioning`, 
        `body`.`body_description`, 
        `mapped`.`body_type_id`, 
        `mapped`.`drive_train`, 
        `fuel`.`fuel_description`, 
        `mapped`.`fuel_id`, 
        `mapped`.`listing_type`, 
        `mapped`.`make_id` 
      from 
        qka_listings as sr 
      left join 
        `qka_promo_listing` as `promolistings` on `promolistings`.`listing_id` = `sr`.`id` 
        and 
        `promolistings`.`status` = 16 
      inner join 
        `qka_listing_item` as `qka_listing_item` on `qka_listing_item`.`listing_id` = `sr`.`id` 
      inner join 
        `qka_automotive` as `mapped` on `mapped`.`item_id` = `qka_listing_item`.`id` 
      left join 
        `qka_rb_body` as `body` on `body`.`id` = `mapped`.`body_type_id` 
      left join 
        `qka_rb_fuel` as `fuel` on `fuel`.`id` = `mapped`.`fuel_id` 
      where 
        `sr`.`state_id` = 6 and `sr`.`category_map_id` = 'active'
      and 
        `sr`.`active` = 0 and `sr`.`pause` = 'active' 
      group by `sr`.`id` 
      order by `sr`.`date_updated` desc) as _make_id 
      inner join 
        `RBMake` as `make` on `make`.`MakeCode` LIKE `_make_id`.`make_id` 
       where (`_make_id`.`make_id` != Null or `_make_id`.`make_id` != '') group by `_make_id`.`make_id`

sr.pause should be 0, sr.active should be active, promolistings.status should be active

Please or to participate in this conversation.