It really makes sense how you explained it. Yes, I skipped that part of
writing raw SQL in PHP applications in my developer carreer, and I went
straight to an ORM (ActiveRecord). And I can imagine picking up bad SQL
habits from it. I wasn't sure if subquery or join was more performant, but
since I'm using Postgres, subquery it is :)
I was actually doing this kind of subquery:
Quiz.where(Question.where(quiz_id:
quizzes_id).where(:title=>/#{query}/i).exists)
But I didn't like that I had to manually repeat the association join. Yours
makes much more sense.
Thank you!
On Sat, Mar 28, 2015 at 4:41 PM Jeremy Evans <[email protected]> wrote:
> On Saturday, March 28, 2015 at 7:31:49 AM UTC-7, Janko Marohnić wrote:
>>
>> Would it be a performance hit to run that ASTTransformer?
>>
>
> Yes.
>
>
>> Yes, I agree that if it were to be implemented on #where, it should be
>> implemented on #order and others. It just feels so natural for me that it
>> works this way, especially coming from ActiveRecord :)
>>
>
> It doesn't feel natural to me. If I use an unqualified identifier in
> ruby, I expect an unqualified identifier in SQL. If I want a qualified
> identifier in SQL, I use a qualified identifier in ruby. If I want to
> automatically qualify unqualified identifiers, I use Dataset#qualify.
>
>
>> As an outside user of the Quiz#search method, I have to remember that it
>> does a join (it could have also been implemented as a subquery instead of a
>> join). So when I'm using Quiz in controller, I have to remember to throw in
>> #qualify before the #search (so that e.g. "created_at" column isn't
>> ambiguous):
>>
>
> If you want to be safe, just like in SQL, you need to manually qualify
> each column (or use Dataset#qualify to do so automatically).
>
>
>> Quiz.newest.in(category).qualify.search(query)
>>
>> I don't know if maybe I have a wrong approach.
>>
>
> You aren't returning any columns from the questions table, and you are
> joining to a 1-n table and using distinct, so I think it would be better to
> use a subquery, assuming you are using a database with a decent subquery
> optimizer. If you use a subquery, you don't need to worry about qualifying
> the columns.
>
> def newest
> reverse(:created_at)
> end
>
> def search(query)
>
>
> where(:id=>Question.select(:quiz_id).where(:name=>/#{query}/i).or(:title=>/#{query}/i)))
> end
>
> I think part of the reason you want this is you picked up bad SQL habits
> from using ActiveRecord. :)
>
> Thanks,
> Jeremy
>
> --
> You received this message because you are subscribed to a topic in the
> Google Groups "sequel-talk" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sequel-talk/_nwq85I1jYs/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> [email protected].
> To post to this group, send email to [email protected].
> Visit this group at http://groups.google.com/group/sequel-talk.
> For more options, visit https://groups.google.com/d/optout.
>
--
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 http://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
