Once you upgrade Sequel, you can try this version. I forgot `avg` returns a
value, where we actually need to `select` the value.
Item.select_append(Review.where(item_id: Sequel[:items][:id]).select {
avg(:rating) }.as(:average_rating))
I used this to confirm it worked, in case you need it:
https://gist.github.com/adam12/24935573836e5a53301707a834adf81c
<https://gist.github.com/adam12/24935573836e5a53301707a834adf81c>
> On Jun 27, 2017, at 11:25 AM, Satyanarayana Gandham
> <[email protected]> wrote:
>
> Hi Adam,
>
> I am using sequel version 4.36.0
>
> In both the cases (using :items__id and Sequel.qualify(“items”, “id”)), I am
> receiving " unknown column 'items.id' in 'where clause' ".
>
> Same is the result when I use "Item.select_append { |p| Review.where(item_id:
> p.items__id).avg(:rating) }" Ref:
> http://sequel.jeremyevans.net/rdoc/files/doc/virtual_rows_rdoc.html#label-SQL-3A-3AQualifiedIdentifiers+-+Qualified+columns
>
> <http://sequel.jeremyevans.net/rdoc/files/doc/virtual_rows_rdoc.html#label-SQL-3A-3AQualifiedIdentifiers+-+Qualified+columns>
>
> Thanks,
> Satya
>
> On Tuesday, June 27, 2017 at 8:42:15 PM UTC+5:30, Adam Daniels wrote:
> Likely my syntax (or your version of Sequel?), as I didn’t have a chance to
> confirm.
>
> You can try symbol splitting, or maybe Sequel.qualify
>
> Item.select_append { Review.where(item_id: :items__id).avg(:rating) }
>
> or
>
> Item.select_append { Review.where(item_id: Sequel.qualify(“items”,
> “id”)).avg(:rating) }
>
> I noticed I passed a singular version of the table name originally so it
> likely wouldn’t of worked anyways.
>
>
>> On Jun 27, 2017, at 11:09 AM, Satyanarayana Gandham <satyanaray...@
>> <>careclues.com <http://careclues.com/>> wrote:
>>
>> Hi Adam,
>>
>> Thanks for the reply. Unfortunately, I am getting an error. '[]' is not
>> defined for Sequel:Module.
>>
>> Regards,
>> Satya
>>
>> On Monday, June 26, 2017 at 9:21:24 PM UTC+5:30, Adam Daniels wrote:
>> I believe you can use `select_append` with a subquery to return a column
>> representing the average rating.
>>
>> Something like:
>>
>> Item.select_append { Review.where(item_id:
>> Sequel[:item][:id]).avg(:rating) }
>>
>> You’ll have to access this field using the Array-style accessor, as it won’t
>> exist as a method on the Item instances.
>>
>>> On Jun 26, 2017, at 10:41 AM, Satyanarayana Gandham
>>> <[email protected] <>> wrote:
>>>
>>> Hi,
>>>
>>> I have two models Item and Review. Item has one_to_many association with
>>> Review. Review has rating column which stores integers from 1 to 10. Rating
>>> of an item equals avg of the rating column in item's reviews_dataset.
>>>
>>> When I list the items, I want to list the rating as well. But the issues
>>> here is that I am calling DB 'N' no. of times for each item. Basically a N
>>> + 1 problem. I have used eager loading for the associations to solve the N
>>> +1 issue for the associations. But, the average here is not an association.
>>> Is there a way I can use eager functionality (or some other way) to load
>>> the average of a column in the associated dataset and not face the N +1
>>> issue.
>>>
>>> Thanks,
>>> Satya
>>>
>>> --
>>> 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 sequel-talk...@ <>googlegroups. <http://googlegroups.com/>com
>>> <http://googlegroups.com/>.
>>> To post to this group, send email to seque...@ <>googlegroups.com
>>> <http://googlegroups.com/>.
>>> Visit this group at https://groups.google.com/group/sequel-talk
>>> <https://groups.google.com/group/sequel-talk>.
>>> For more options, visit https://groups.google.com/d/optout
>>> <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 https://groups.google.com/group/sequel-talk
>> <https://groups.google.com/group/sequel-talk>.
>> For more options, visit https://groups.google.com/d/optout
>> <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]
> <mailto:[email protected]>.
> To post to this group, send email to [email protected]
> <mailto:[email protected]>.
> Visit this group at https://groups.google.com/group/sequel-talk
> <https://groups.google.com/group/sequel-talk>.
> For more options, visit https://groups.google.com/d/optout
> <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 https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
