I'm having problems with this query, which I've put on Github Gist for syntax highlighting so you can see what's going on better.
What I want to do, is join the groups table to the posts table, which I've done. But I only want to retreive the tables that have their pond column, being more than 1.
On Saturday, October 20, 2012 3:44:00 PM UTC-7, desbest wrote:
> I'm having problems with this query, which I've put on Github Gist for > syntax highlighting so you can see what's going on better.
> What I want to do, is join the groups table to the posts table, which I've > done. But I only want to retreive the tables that have their pond column, > being more than 1.
Your first error is "SQLite3::SQLException: no such column: posts.datenumber", which should be obvious, that column doesn't exist in that table. You didn't post the schema for the tables involved in the query, but if I had to guess, you want groups__datenumber instead of posts__datenumber.
Your second error is "comparison of Symbol with Integer failed", which is because you are on ruby 1.9 and Sequel doesn't override the Symbol#> method. Use a virtual row, switch "exclude(:posts__pond > 0)" to "exclude{posts__pond > 0}".
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 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?
On 21 October 2012 02:52, Jeremy Evans <jeremyeva...@gmail.com> wrote:
> On Saturday, October 20, 2012 3:44:00 PM UTC-7, desbest wrote:
>> I'm having problems with this query, which I've put on Github Gist for
>> syntax highlighting so you can see what's going on better.
>> What I want to do, is join the groups table to the posts table, which
>> I've done. But I only want to retreive the tables that have their pond
>> column, being more than 1.
> Your first error is "SQLite3::SQLException: no such column:
> posts.datenumber", which should be obvious, that column doesn't exist in
> that table. You didn't post the schema for the tables involved in the
> query, but if I had to guess, you want groups__datenumber instead of
> posts__datenumber.
> Your second error is "comparison of Symbol with Integer failed", which is
> because you are on ruby 1.9 and Sequel doesn't override the Symbol#>
> method. Use a virtual row, switch "exclude(:posts__pond > 0)" to
> "exclude{posts__pond > 0}".
> To post to this group, send email to sequel-talk@googlegroups.com.
> To unsubscribe from this group, send email to
> sequel-talk+unsubscribe@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sequel-talk?hl=en.
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 > 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.
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.
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.
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 <jeremyeva...@gmail.com> 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.
> To post to this group, send email to sequel-talk@googlegroups.com.
> To unsubscribe from this group, send email to
> sequel-talk+unsubscribe@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sequel-talk?hl=en.
> 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.
> 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.
> 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 <jeremyeva...@gmail.com> 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.
>> To post to this group, send email to sequel-talk@googlegroups.com.
>> To unsubscribe from this group, send email to
>> sequel-talk+unsubscribe@googlegroups.com.
>> For more options, visit this group at
>> http://groups.google.com/group/sequel-talk?hl=en.
