That's great! SQL inside the 'has' did the trick! Thanks a lot!
Items model:
has_many :item_variations
...
has 'CASE WHEN item_variations.item_photo_id IS NULL THEN items.picked_at
ELSE item_variations.picked_at END', as: :combined_picked_at, type:
:datetime
Search controller
items_found = Item.search params[:q], :joins => 'left join item_variations
iv on iv.item_id=items.id',
:select => 'items.*, iv.id as variation_id', :order =>
'combined_picked_at DESC'
Now, I wanted to get items joined with item variations (see how I use left
join). In ActiveRecord I would get regular *items* rows along with extra
data from *item_variations* table. That is
items_found[0]:
id: 1
variation_id: 3434
items_found[1]:
id: 1
variation_id: 9898
items_found[2]:
id: 2
variation_id: nil
How can I gather this in Thinking Sphinx? It looks like my *joins*
statement works, but it only picks first item variation, i.e.
items_found[1] is a different item (has different id, unlike
items_found[0]).
Thank you!
On Monday, 3 November 2014 18:37:00 UTC+2, Misha Slyusarev wrote:
>
> I would like to be able ordering by a calculated field. Which is
> constructed from a field in current table and if it's empty use a field
> from a associated table.
>
> I used to use case when <http://dev.mysql.com/doc/refman/5.0/en/case.html> in
> my regular Rails requests.
>
> items.joins('left join item_variations iv on iv.item_id=items.id').
> select('case when iv.item_photo_id is not null then iv.picked_at else
> items.picked_at end as combined_picked_at')
>
> Now I'm looking for a way to use similar technique in Sphinx. Is it
> possible?
>
--
You received this message because you are subscribed to the Google Groups
"Thinking Sphinx" 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/thinking-sphinx.
For more options, visit https://groups.google.com/d/optout.
