Ambiguous columns

[ashandi]

Hello!

I have a database with next structure:

table orders:
    id;
    order_number;
    domain_id;
    user_id;
    other_fields;

table users;
    id;
    login;
    domain_id;
    other_fields;

In my Order Model I put next code:

protected $searchable = [
        'columns' => [
            'orders.order_number' => 100,
            'orders.lastname' => 100,
            'orders.email' => 100,
            'users.login' => 70,
            'orders.postcode' => 50,
            'orders.phone' => 50,
            'orders.city' => 50,
            'orders.street' => 50,
        ],
        'joins' => [
            'users' => ['orders.user_id','users.id'],
        ]
    ];

As the result, I caught next error:

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'domain_id' in where clause is ambiguous

The reason of this error is duplicate column 'domain_id' in the both tables.

Is there any way to choose columns of 'users' table which will be joining at the searching?

[gabrielbuzziv]

did you solve it?

[ashandi]

No, I chose another package.

[markwalet]

Which package did you choose? Happy with it?

[ashandi]

I started to use elasticsearch. It is really cool tool.

[Tamrael]

as the error clearly states the domain_id is in the where clause but you don't have it in the searchable columns it has nothing to do with this package but with your query that gets search applied to it. simply prefix the domain_id with the correct table (orders.domain_id) in the where and all should be golden

the problem would we present in any join you make between these two tables

[ashandi]

@Tamrael , thanks for your answer!

But I don't know how to control the query. I just put the $searchable array to my Order model and called:

Order::search('any string', null, true)->get(); 

and I caught this error. Query was totally constructed by this package.

[Tamrael]

@ashandi are there any scopes on the order model that use the domain_id? in general the query generated only includes the fields in the where that already were present when search() was called. the fields in the searchable column are used in a select but that's about it.

quite strange you got this error but since you moved to another plugin i figure you can't reproduce the old behavior without much hassle

[ashandi]

@Tamrael I real good read the Order model and saw that there was a scope

    public function scopeForDomain($query, int $domainId)
    {
        return $query->where('domain_id', $domainId);
    }

I think the error was caused by this. I'm really sorry that I wasted your time, but it's good that we solved this problem.

[Justicea83]

ambiguous columns I dont even want that column involved in the searches 'columns' => [ 'students.fname' => 10, 'students.lname' => 10, 'students.mname' => 10, 'students.school_id'=>0, 'transactions.school_id'=>0, ], 'joins' => [ 'students' => ['transactions.student_id','students.id'], ], it still gives errors