Where on join query with alias not work

[maxwkf]

Where on join query with alias not work

When a where clause apply on join query with alias, it cannot parse the correct where.

Let us consider the following query.

PHP Query

$query = Entry::query()
  // join self as e
  ->join('entries as e',fn($join) => $join
  ->whereColumn('e.id', 'entries.id')
  ->where('e.collection', 'properties')
  ->where('e.published', 1)
);
// join locations
$query->leftJoin('entries as locations', function($join) {
    $join
        ->where('locations.collection', 'locations')
        ->on('locations.id', 'e.data->location')
        ;
});
// where on joint clause
$query
  // this is expected to search on the JSON data field for property_type
  ->where('e.data->property_type', 'lodge')
  // this is expected to search on the locations slug for value 'shaldon'
  ->where('locations.slug', 'shaldon')
;

SQL Generated

The where property_type putting the e.data in the json key field but not the data field

select * from `entries`
  inner join `entries` as `e`
    on `e`.`id` = `entries`.`id` and `e`.`collection` = 'properties' and `e`.`published` = '1'
  left join `entries` as `locations`
    on `locations`.`collection` = 'locations' and `locations`.`id` = json_unquote(json_extract(`e`.`data`, '$."location"'))
  where
    json_unquote(json_extract(`data`, '$."e.data"."property_type"')) = 'lodge' // Wrong SQL
    and json_unquote(json_extract(`data`, '$."locations.slug"')) = 'shaldon' // Wrong SQL

SQL Expected

select * from `entries`
  inner join `entries` as `e`
    on `e`.`id` = `entries`.`id` and `e`.`collection` = 'properties' and `e`.`published` = '1'
  left join `entries` as `locations`
    on `locations`.`collection` = 'locations' and `locations`.`id` = json_unquote(json_extract(`e`.`data`, '$."location"')) 
  where
    json_unquote(json_extract(`e`.`data`, '$."property_type"')) = 'lodge' // SQL expected
    and `locations`.`slug` = 'shaldon' // SQL expected

Cause

The problem caused by the column check in src/Entries/EntryQueryBuilder.php.

protected function column($column)
{
    if ($column == 'origin') {
        $column = 'origin_id';
    }

    if (! in_array($column, self::COLUMNS)) {
        $column = 'data->'.$column;
    } else {
        $column = parent::column($column);
    }

    return $column;
}