Anorm and a self-join

[Alvaro Carrasco]

Using the parsers, how can I extract the values of columns when doing a self-join?

When I try to do something like this:

SQL("SELECT c.id as c_id, parent.id as p_id FROM users c INNER JOIN users p ON c.parent_id = p.id").as {

        long("c_id") ~ long("p_id") *

}

I get the following error:

java.lang.RuntimeException: c_id not found, available columns : users.id, users.id

I have also tried:

long("id") ~ long("id")

and

long("users.id") ~ long("users.id")

Both of which unfortunately but predictably return the value of the id from the first table twice.

Alvaro

[MG]

Have you tried this:

SQL("SELECT c.id as c_id, p.id as p_id FROM users c INNER JOIN users p ON c.parent_id = p.id").as {

        long("c.id") ~ long("p.id")  *

}

-MG

[Guillaume Bort]

There is a pending pull-request allowing to use aliases instead of
column names in Anorm.

> --
> You received this message because you are subscribed to the Google Groups
> "play-framework" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/play-framework/-/2bsU5ir0bdAJ.
>
> To post to this group, send email to play-fr...@googlegroups.com.
> To unsubscribe from this group, send email to
> play-framewor...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/play-framework?hl=en.



--
Guillaume Bort, http://guillaume.bort.fr

[Elie Dac]

Hi,

Still no news about anorm and self join ?

For now, the only workaround I am aware of, is to select your data with two queries... :/

So this could be much worst for the performance.

Does anyone still have no "trick" to be able to select two different fields with the same name?

SELECT table.id, parent.id FROM table

JOIN table parent ON table.parent_id = table.id

Thanks!

Elie

[Daniel Manchester]

Hi,

It's not pretty, but perhaps you could create a view that selects all columns from your table and then join the table to that view? Performance-wise, I would expect that to be comparable to the self-joining query.

The main downsides are, you'd have an extra object cluttering your schema, and you'd probably need to drop and re-create the view when making structural changes to the underlying table.

Dan