#SQLSTATE[23000]: Integrity constraint violation: Column is ambiguous

18 messages · Page 1 of 1 (latest)

coarse ruin
#

If you're joining two tables both tables could have a created_at

#

So you pre append the table name to avoid confusion

quartz smelt
#

Why doesn't Laravel automatically add the table name in front of the field name? In my example, it can be assumed that it is about the post table.

weary yarrow
#

If it's joined manually, you might consider using whereHas() or whereRelation() in stead. Those don't use joins, so you wouldn't get any ambiguous columns.

quartz smelt
#

I use a relationship to join the tables: $this->hasManyThrough(Post::class, Employee::class)

#

This is the structure of the database:

image.png
quartz smelt
simple wadi
#

Tell me how would laravel know which column to pick? Even if it did one strategy may work for your use case but will cause issues in other scenarios.

And it’s better to be explicit. For code readability.

quartz smelt
#

In my case, Laravel can assume that the posts table is meant, because everything is based on the post model. If the field of another table is meant, this can be specified in the methods (see here). In the case of code readability, this is even better, especially for very large and complex queries.

It is a problem to write the table name statically in front of the field, because if the table name is changed, this string may not be considered and therefore not changed. In the case of code readability, this is even better, especially for very large and complex queries.

strong rapids
#

when you say I create a query with Eloquent, which looks roughly like this: would do you mean by roughly? Can you show the exact query. I don't believe it's to do with the withCount. It's trying to join employees somewhere?

simple wadi
# quartz smelt In my case, Laravel can assume that the `posts` table is meant, because everythi...

"It is a problem to write the table name statically in front of the field, because if the table name is changed, this string may not be considered and therefore not changed.))."

And if the column name changed? Laravel would all of a sudden magically happen to know the new column name? You contradict what you say.

"In the case of code readability, this is even better, especially for very large and complex queries."
For large and complex queries it is always better to be specific, which is why its considered good practice in SQL. If your code is organised then there should be no pain having to refactor such a thing.

quartz smelt
#

This is the whole query:

Company::find(ID)
    ->posts()
    ->withCount("histories as views") # post_histories
    ->orderByDesc("views")
    ->withCount([
        "comments", # post_comments
        "reactions as l" => function (Builder $query) { # post_reactions
            $query->where("is_like", true);
        },
        "reactions as dl" => function (Builder $query) { # post_reactions
            $query->where("is_like", false);
        }
    ])
    ->whereBetween("created_at", [$this->fromDate, $this->toDate]) # triggers the issue
    ->limit(3)
    ->get();

Company.php:

public function posts()
{
    return $this->hasManyThrough(Post::class, Employee::class);
}

This is the more specific database-structure:

image.png
quartz smelt
# simple wadi `"It is a problem to write the table name statically in front of the field, beca...

If the table name is changed and Laravel automatically sets the value of the variable $table in front of the fields, this means significantly less work to change other code.
This is not possible when changing the names of the fields, but here we are talking about the table names.

I don't understand your last point. In my previous linked example I don't need a table name in front of the fields and it is still specific and clear which table it is.

quartz smelt
quartz smelt
#

Can anyone else comment on this? In general, the question is why Eloquent does not automatically write the table name in front of the field. (see my original post)

wild jay
#

Hi, not getting any traction on my post here of a similar time so I thought I'd comment (not a dig)

I see this quite a bit in rails too so I suspect it's not particular to laravel and likely due to the nature of using a DBAL.

I suspect non-trivial to solve without causing edge/corner cases and left as an "exercise for the reader"

simple wadi