Quick question : Option queryAttributes - ambiguous column name

[robbykrlos]

This is a question.

Prerequisites

• [ 4.7.1] Are you running the latest version?
• [x] Are you reporting to the correct repository?
• [x] Did you check the documentation?
• [x] Did you perform a cursory search?

Description

Hi,

Quick question: If in the option query for my modelA I have a join with modelB, and both models have an attribute / a column "name", when I select the option $queryAttributes to ['name'] the query will fail with SQL error that the name is ambiguous, so my initial reaction was to set the table name as my_table.name.

But this is interpreted as isNested and the query changes to $myModel->my_table()->name.

Is there a workaround for this?

I just need my Option select for ModelA to have searchable column 'name', but with some relations to modelB.

PS: trying aliases will not work for WHERE clauses.

Thank you,

[aocneanu]

could you post the base query here?

[robbykrlos]

I just realized I forgot an important clue in the scenario above. Error is given when I use the select search function

SQL with search:

select lb_virtuals.id, lb_virtuals.name from `lb_virtuals` 
left join `lb_pools` on `lb_virtuals`.`pool_id` = `lb_pools`.`id` 
where ((`name` LIKE %test%))
group by `lb_virtuals`.`name` 
order by `name` asc 
limit 100

lb_pools has a column "name".

Error:

{,…}
exception: "Illuminate\\Database\\QueryException"
file: "..\\vendor\\laravel\\framework\\src\\Illuminate\\Database\\Connection.php"
line: 678
message: "SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'name' in where clause is ambiguous (SQL: select \n            lb_virtuals.id,\n            lb_virtuals.name\n         from `lb_virtuals` left join `lb_pools` on `lb_virtuals`.`pool_id` = `lb_pools`.`id` where ((`name` LIKE %test%)) and `lb_virtuals`.`id` not in (1) group by `lb_virtuals`.`name` order by `name` asc limit 100)"

Options.php for "Virtuals":

class Options extends Controller
{
    use OptionsBuilder;

    protected $model = Virtual::class;

    protected $queryAttributes = ['name'];

    public function query(Request $request)
    {
        $request['trackBy'] = 'lb_virtuals.id';

        return Virtual::selectRaw('
            lb_virtuals.id,
            lb_virtuals.name
        ')
            ->leftJoin('lb_pools', 'lb_virtuals.pool_id', '=', 'lb_pools.id')
            ->groupBy(
                'lb_virtuals.name'
            );
    }
}

And as I said, adding the table name to $queryAttributes like

protected $queryAttributes = ['lb_virtuals.name'];

will not work since isNested will read things differently...

[robbykrlos]

I was thinking maybe this is not something you want to look into, since this is not a problem for you. In this case, can I ask for some flexibility on class exposure -> binding. At the moment most methods are private and class vendor/laravel-enso/select/src/Services/Options.php is instantiated with new in the only place :(

vendor/laravel-enso/select/src/Traits/OptionsBuilder.php

private function response(Request $request)
    {
        ...
        return (new Options($query))

As always, I can contribute.

[robbykrlos]

Created PR : Quick question : Option queryAttributes - ambiguous column name #48 #49

[robbykrlos]

I'll close this ticket since my issue with OptionsBuilder can be solved for my situation with a custom OptionsBuilder.

Thanks @aocneanu for the fast PR and merge into master 👍🏼