After enough time I managed to fix everything Jeremy and get the query I want. Thanks for your help about using an alias to join to a subselect.
On 21 October 2012 20:28, tynamite <[email protected]> wrote: > Thanks alot but that wasn't the answer I was looking for with my question > as I had changed my original sql query to be more specific. > Disregard my previous messages. Here's the completely new question. > > I've improved my sql to > > @groups = Group.where().group(:groups__id).left_join(:posts, >> :group_id=>:id) > > .order(Sequel.desc(:groups__id)) > > .select(:groups__id, :groups__user_id, :groups__title) >> > > What I would like to do, is order the groups on my website, based on the > *recent > activity* within those groups. > Right now *(in the query above)* the groups are ordered by their id > column in descending order. > I would like to improve this, by *also *ordering them by the latest post > made inside them, also sorted by a descending id column. > > Here's what I tried to use that didn't work. > > @groups = Group.where().group(:groups__id).left_join(:posts, >> :group_id=>:id) > > .order(Sequel.desc(:groups__id))*.order_prepend{max(posts__datenumber)*** > > .select(:groups__id, :groups__user_id, :groups__title) >> > > So far, that hasn't changed anything. > I created a row in the posts table, with the value of 1 for the group_id > column, and it hasn't done anything. > I would like to be matched up to the group's id of 1, and be used for > sorting those groups. > > Could you please help? > > On 21 October 2012 17:08, Jeremy Evans <[email protected]> wrote: > >> On Sunday, October 21, 2012 1:36:58 AM UTC-7, desbest wrote: >> >>> For the first error, I would like to say that the posts.datenumber and >>> groups.datenumber column *does* exist. I probably should have made that >>> clear, >>> I've added the schema now. >>> https://gist.github.com/**3925058<https://gist.github.com/3925058> >>> When I use the virtual row, it started working to do what I wanted it to >>> do. Thank you. It was a query to order groups based on recent activity, >>> excluding comment replies. >>> >>> This leads me to my 2nd question. >>> >>> How can I modify the first query that originally worked, so that >>> inbetween .order{max(posts__datenumber) and >>> .order_append(:groups__**datenumber), >>> that posts with a pond with more than zero 0, be excluded from the ordering >>> of the rows, or that the groups with a post above zero, gets lower >>> presedence in sorting? >>> >> >> You are joining to a subselect, which requires using an alias (Sequel >> creates uses a default alias if you don't specify one), you probably want >> "join(Post.where{floor > 0}.as(:posts), :group_id=>:id)". The current code >> fails because in the query, posts.datenumber is not valid. Also, you >> should probably remove the ".exclude()". >> >> Note that you can call the sql method on any dataset to see the SQL it >> would generate, which would make it more obvious why SQLite didn't like >> your SQL. >> >> Jeremy >> >> -- >> You received this message because you are subscribed to the Google Groups >> "sequel-talk" group. >> To view this discussion on the web visit >> https://groups.google.com/d/msg/sequel-talk/-/IPmxY5Y5_YoJ. >> >> To post to this group, send email to [email protected]. >> To unsubscribe from this group, send email to >> [email protected]. >> For more options, visit this group at >> http://groups.google.com/group/sequel-talk?hl=en. >> > > -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sequel-talk?hl=en.
