ERROR: column reference "question_id" is ambiguous

[Freewind]

I met a difficult problem, and don't know what to do.

I have 4 classes:
1. User
2. Question
3. Answer
4. Vote

user can create questions and answers, and also can vote questions and
answers. Each of them has an "id" column.

I have a query:

Ebean.find(Vote.class).where().eq("question_id",
questionId).eq("user_id", userId).findUnique()

Sometime, it runs correctly, generate a simple SQL like:
"select * from votes where question_id=? and user_id=?"

But sometimes, it generate a very complex SQL like:

select v.id as c0, v.value as c1
, vq.id as c2, vq.vote as c3
, vqv.id as c4
, va.id as c5
from votes v
left outer join questions vq on vq.id = v.question_id
left outer join votes vqv on vqv.question_id = vq.id
left outer join answers va on va.id = v.answer_id
where question_id = ? and user_id = ?

Now, there are 4 tables! And three of them have a "question_id"(2
votes, 1 answers), so the error occurs:

ERROR: column reference "question_id" is ambiguous

It's very hard to debug, because the SQL generated not always the
same, and I don't know why it will generate such a complex SQL.

Help! I'm crazy now

[Daryl Stultz]

On Aug 14, 5:25 am, Freewind <nowind...@gmail.com> wrote:
> I met a difficult problem, and don't know what to do.

...

> Each of them has an "id" column.
>

> Ebean.find(Vote.class).where().eq("question_id",
> questionId).eq("user_id", userId).findUnique()

I'm going to make some guesses here, if I'm right it will help you
out. Your database table for questions has the primary key named
"question_id". Your Question class has a field named "id" for the @Id
annotation. You are using SQL column names rather than ORM association
paths. You want to use this instead:

Ebean.find(Vote.class).where()
.eq("question.id", questionId)
.eq("user.id", userId)
.findUnique()

Ebean will parse "question.id" and figure out you want the
Vote.getQuestion().getId() field. If Ebean cannot match the property/
path (question_id) it will simply pass the string straight through to
the generated query (hence it works in one case but not another). If
you understand what's going on, it's a very cool feature because you
can mix raw SQL in with the ORM querying. If you don't understand,
well you get confused.

> Sometime, it runs correctly, generate a simple SQL like:

...

> But sometimes, it generate a very complex SQL like:

What you are probably seeing here are the effects of autotuning. Ebean
tracks the usage of the object graphs and tunes the queries to be more
efficient.

You might find it interesting that if you have objects for the user
and question (rather than just the integer id's) you can do this:

Ebean.find(Vote.class).where()
.eq("question", question)
.eq("user", user)
.findUnique()

/Daryl

[Freewind]

@Daryl, thank you so much!

From your answers, it seems there are a lot more features than I
thought, I will try it later. I have looked for these in the User-
Guide, but not found. Thanks again!

[Freewind]

For this question, can I treat it as a kind of bug?

From this code:

Ebean.find(Vote.class).where().eq("question_id",
questionId).eq("user_id", userId).findUnique()

We can clearly know that the "question_id" and "user_id" are belongs
to the votes table(or the first one if there are many). If it generate
some "outer-join" SQL, it should add correct alias to these columns.
Or it might be throw the "ambiguous" error in some special conditions,
but hard to test.

> > /Daryl- 隐藏被引用文字 -
>
> - 显示引用的文字 -

[Daryl Stultz]

On Aug 14, 10:49 am, Freewind <nowind...@gmail.com> wrote:
> I have looked for these in the User- Guide, but not found.

True, it's not documented.

> For this question, can I treat it as a kind of bug?

No, it's a *feature*, not a *bug* :-) here's why.

In a "standard" ORM query language, it is the object model that is
queried, not the database model. This is what your query might look
like in JPA:

select v from Vote as v
where v.question_id = :qId
and v.user_id = :uId

(then set the named parameters for the question id and user id)

This query would fail the parsing phase (in the ORM, before going to
SQL) because "question_id" is not a property of Vote. The query should
be like this:

select v from Vote as v
where v.question.id = :qId
and v.user.id = :uId

So JPA would fail to parse question_id (and user_id) and throw an
error. Here, Ebean fails to parse question_id but instead of throwing
an error, it passes the unparseable content through to the generated
SQL. Here's the feature part. Imagine you have a custom function in
the database called "transmogrify", and you wrote a query like this:

Ebean.find(Vote.class).where().eq("transmogrify(user.name)",
"XYZ123")...

Assuming User has a "name" field, Ebean would parse user.name and
generate the appropriate SQL along with necessary joins to properly
reference it. But Ebean fails to parse transmogrify and passes it
through to the SQL. The result is that you can mix SQL with the ORM.
So you might end up with a SQL query like this:

select v.id as c1, ...
from Votes as v
join Users as u on u.user_id = v.user_id
where transmogrify(u.name) = ?

The "problem" with this feature is that when you make a mistake, the
error thrown (by the database driver) is not very helpful.

Ebean.find(Vote.class).where().eq("ufer.fame", "XYZ123")...

Here I've spelled user.name wrong and Ebean just sends it through and
I have to look a little harder to figure out what went wrong.

/Daryl

[Freewind]

OK, let's say it's a feature :) Thank you DaryI