John,
That's an interesting thought. A bit more details below. Would appreciate
if you have more insight:
I'm basically building an analytics database using a JSON API on one side
and Postgresql on the other. So on one side I'm getting order_id, item_id,
and quantity, and other fields via JSON API (no primary key, unique key is
order_id, item_id, quantity). I am trying to see if they exist in my
database. If yes, update the record, if not, create them. So if I go with
your idea, I could conceptually do something like this:
First problem, pull the item_id from each order_item. Forgive my, I'm also
a Ruby newbie :), so your statement:
item_ids = orders_items_to_match.map { |order_item|
order_item['orderItemId'] }
would still work if orders_items_to_match is an array and each order_item
is a hash. I believe in Sequel land they are right?
Select order_items where order_id = :json_order_id and product_id
in(json_product_id array)
Loop on JSON order_items then sub-loop on Sequel order_items to check for
matches and update or insert.
I was trying to avoid the nested loop. Although each array is on average
going to be only 2-5 items, maybe 30 max. So, looping is probably still
faster than that many db roundtrips.
On Wed, Nov 20, 2019 at 9:50 PM John W Higgins <[email protected]> wrote:
>
>
> On Wed, Nov 20, 2019 at 1:21 PM Mason Kimble <[email protected]>
> wrote:
>
>> Basically, can I filter a dataset after it goes to the database?
>>
>> order_items = OrderItem.dataset.where(order_id: orderId).all (I know this
>> would query database)
>>
>> orders_items_to_match.each { | match_item |
>> matching_item = order_items.where(item_id:
>> match_item['orderItemId']).first (Does this go back to database or in
>> memory)
>> }
>>
>>
> I believe you are looking to do the work on the wrong side.
>
> Something like this feels like it would work for you (typos are my middle
> name)
>
> # collect items were are searching
> item_ids = orders_items_to_match.map { |order_item|
> order_item['orderItemId'] }
>
> # query using the items as an IN clause
> OrderItem.dataset.where(order_id: orderId).where(item_id: item_ids).all
>
> That should go to the database and select against both the order_id and
> the item_ids you are looking for.
>
> John
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sequel-talk/CAPhAwGx-uHn_y84s-YzA9Tb9LZVm1ZJOkso95FH5Ti0GUXtmtA%40mail.gmail.com
> <https://groups.google.com/d/msgid/sequel-talk/CAPhAwGx-uHn_y84s-YzA9Tb9LZVm1ZJOkso95FH5Ti0GUXtmtA%40mail.gmail.com?utm_medium=email&utm_source=footer>
> .
>
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/CAEWSjwJD2X9ecgi_sU%3DxfA81oiMBhth_e3VyYzeUJsry21fdVg%40mail.gmail.com.
