On Saturday, October 14, 2017 at 2:43:48 PM UTC-7, Carlos Azuaje wrote:
>
> I have problems with creating a query that can relate 4 tables.
>
> This is the schema:
>
> "users" - | id |
>
> "snippets" - |id| user_id |
>
> "snippets_has_tags " - |snippets_id | tags_id|
>
> "tags" - | id | name | description|
>
> I would like to be able to consult the amount of tag that all the snippets
> of a user have and also be able to count the repetitions of these tags, i
> mean,
> if in a user snippet is the tag "javascript" and in another "javascript"
> and also "python"
> I want the query to return:
>
> (javascript, 2), (python, 1)
>
> This is the code:
>
> DB[:users]
> .join(DB[:snippets], user_id: Sequel.qualify(:users, :id))
> .join(DB[:snippets_tags], snippet_id: Sequel[:snippets][:id])
> .join(DB[:tags], id: Sequel[:snippets_tags][:tag_id])
> .where(:user_id=>user.id).select(:name,
> Sequel[:tags][:description]).all.to_json
>
>
> But, i get this exception: *Sequel::DatabaseError: SQLite3::SQLException:
> no such column: snippets.id <http://snippets.id>*
>
>
You should probably avoid the use of joins to subqueries, unless you are
aliasing the subqueries. You can also lead Sequel handle the qualification
for join conditions automatically in this case.
DB[:users]
.join(:snippets, user_id: :id)
.join(:snippets_tags, snippet_id: :id)
.join(:tags, id: :tag_id)
.where(:user_id=>user.id).select(:name,
Sequel[:tags][:description]).all.to_json
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
