JPA: Mapping a Join Table with Order Column?

[Barsum]

I have a model that looks like this:

@Entity
public class Service extends Model {
@ManyToMany
public List<Employee> employees;
}


@Entity
public class Employee extends Model {
public String name;
}

i.e. a service has many employees that can perform that service. JPA
creates a relation table: service_employee
.
To extract all employees that can perform a certain service I use:

return Service.find("SELECT e FROM Service s JOIN s.employees e WHERE
s = ?", this).fetch();

This works fine. But the order of the employees is lost.

e.g. service.employees = [3,2,1] but the query result is [1, 2, 3]

The obvious way would be to somehow retain the order from the relation
table (service_employee) - but is that possible? An alternative would
be to insert a orderBy field in the relation table, but this seems
like a lot of work, and I'm afraid it will break the simplicity of my
queries.

So my question is: How can i retain the order that the employees are
inserted into service when I extract all employees of that service?

Thanks in advance

[Francisco Canedo]

On Thu, Nov 03, 2011 at 02:33:27AM -0700, Barsum wrote:
…

> return Service.find("SELECT e FROM Service s JOIN s.employees e WHERE
> s = ?", this).fetch();
>
> This works fine. But the order of the employees is lost.
>
> e.g. service.employees = [3,2,1] but the query result is [1, 2, 3]

Off the top of my head, ' order by s.id' after '?' is what you want.

--
Francisco Canedo

-----

"There are, it has been said, two types of people in the world. There
are those who, when presented with a glass that is exactly half full,
say: this glass is half full. And then there are those who say: this
glass is half empty.
The world belongs, however, to those who can look at the glass and
say: 'What's up with this glass? Excuse me? Excuse me? This is my
glass? I don't think so. My glass was full! And it was a bigger glass!"

Terry Pratchett, The Truth

[Sebastien Cesbron]

Hi

With JPA 2.0 you can use @OrderColumn annotation but you need a column to store that order

http://wiki.eclipse.org/EclipseLink/Examples/JPA/2.0/OrderColumns

Regards
Seb

2011/11/3 Barsum <droi...@gmail.com>

--
You received this message because you are subscribed to the Google Groups "play-framework" group.
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.

[Barsum]

Thanks to you both, but that's not entirely what I'm looking for.

Let me rephrase my question:

Is there a simple way to make sure that the order of a collection is
the same as the order the elements where added?

i.e.

service.employees.add(john)
service.employees.add(mark)
service.employees.add(benny)

service.employees.iterator() -> john, mark, benny

On 3 Nov., 10:48, Sebastien Cesbron <scesb...@gmail.com> wrote:
> Hi
>
> With JPA 2.0 you can use @OrderColumn annotation but you need a column to
> store that order
>
> http://wiki.eclipse.org/EclipseLink/Examples/JPA/2.0/OrderColumns
>
> Regards
> Seb
>

> 2011/11/3 Barsum <droid...@gmail.com>

[Sebastien Cesbron]

Hi

For me the annotation @OrderColumn is what you are looking for.

The order column will be valued when you insert your objects to ensure that when you retrieve them they will be in the same order.

Regards
Seb

2011/11/3 Barsum <droi...@gmail.com>

Thanks to you both, but that's not entirely what I'm looking for.

[Barsum]

Hi Sebastien,

I appologise for not listening the first time. You're absolutely
right, and solved my problem.

Thanks a bunch

On 4 Nov., 08:06, Sebastien Cesbron <scesb...@gmail.com> wrote:
> Hi
>

> For me the annotation @OrderColumn is what you are looking for.
>
> The order column will be valued when you insert your objects to ensure that
> when you retrieve them they will be in the same order.
>
> Regards
> Seb
>

> 2011/11/3 Barsum <droid...@gmail.com>

[Mike]

I have tried this and IT DOES NOT WORK. I use Play 2.0.4.

@ManyToMany
@OrderColumn(name="my_rank")
private List<...> ...

The join table contains the Ids of both objects, but NO WAY to get any ordering column (the documentation says it would be named xxxx_ORDER by default or the given name ("my_rank").

I am thinking that the choice of Play was a big mistake.

[Łukasz Groszkowski]

I have encountered the same problem yesterday. Any updates on that? I'm downloading 2.1.2 RC in hope they've fixed it.

And it is so damn frustrating. Unfortunately I also have serious doubts if choosing play was right.