Re: [DataMapper] Sort records by column in another table?

[Arthur M. Meskelis]

I think that you can do like this

BookReader.first.books.all(:order => [:published.desc])

So, books is connected to BookReader through checkouts. You will take the books that are checkouts.

So long

2012/9/3 armanx <arm...@gmail.com>

I'm looking to sort records in one table, based on a date column in another table. For example, I have the following tables:

class BookReader
include DataMapper::Resource
has n, :checkouts
has n, :books, :through => :checkouts
property :id, Serial
property :name, String
property :city, String
end
class Book
include DataMapper::Resource
has n, :checkouts
has n, :readers, :through => :checkouts
property :id, Serial
property :title, String
property :author, String
property :published, Date
end
class Checkout
include DataMapper::Resource
belongs_to :bookreader, :key => true
belongs_to :book, :key => true
property :created_at, DateTime
property :updated_at, DateTime
end

(This assumes each BookReader can only check out each Book once).

I can select all the Book objects belonging to a particular BookReader:

checkouts = BookReader.first.checkouts

Now, I want to sort these based on the :published date of the Book object. E.g.:

SELECT
c.*, b.date
FROM
"checkouts" as c
JOIN "books" as b on b.id = c.book_id
WHERE
c.bookreader_id = 1
ORDER BY
b.date DESC

Any way to do this directly through DM?

--
You received this message because you are subscribed to the Google Groups "DataMapper" group.
To view this discussion on the web visit https://groups.google.com/d/msg/datamapper/-/SS4rSLbWrKQJ.
To post to this group, send email to datam...@googlegroups.com.
To unsubscribe from this group, send email to datamapper+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/datamapper?hl=en.

--
------------------------------------------
 1. Notebook para você ficar livre e programar a vontade: R$ 2300
 2. Curso de Programação/SysAdmin/DBA: R$ 5000
 3. Pedir a solução pronta para um problema numa lista de discussão: Não tem preço !

E para todas as outras existe RTFM, STFW e  LMGTFY

[armanx]

That doesn't quite accomplish what I'm trying to do. 

BookReader.first.books.all(:order => [:published.desc])

gives me Books; what I'm after are Checkouts, sorted by Book's :published column

[Arthur M. Meskelis]

oh, sorry.

That's a hard to do.

I found that site http://rhnh.net/2010/12/01/ordering-by-a-field-in-a-join-model-with-datamapper maybe help.

Anyway, I will continue to search.

2012/9/4 armanx <arm...@gmail.com>

--

You received this message because you are subscribed to the Google Groups "DataMapper" group.

To view this discussion on the web visit https://groups.google.com/d/msg/datamapper/-/Nayx-lp5oksJ.

To post to this group, send email to datam...@googlegroups.com.
To unsubscribe from this group, send email to datamapper+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/datamapper?hl=en.

[Arthur M. Meskelis]

armanx, I think that I found it.

BookReader.first.checkouts.all(links: [Checkout.relationships[:book].inverse], order: [DataMapper::Query::Direction.new(Book.properties[:published], :desc)])

freakish

But it retrieves me a collection of Checkout objects, ordered by :published.desc .

is that what you want ?

so long

http://datamapper.lighthouseapp.com/projects/20609/tickets/1164-manytoone-relationships-form-bad-sql-with-links 

2012/9/4 Arthur M. Meskelis <hallofr...@gmail.com>