Re: Filtering & Subqueries

[BeeRich33]

Excellent.  Thank you.  

Is the overhead much more with found sets of primary keys using an IN 
?  

Cheers


-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sequel-talk/a19ba8b4-8b5e-4d5d-bde5-a3f8c3970ce0%40googlegroups.com.

Re: Query Error

[Jeremy Evans]

On Tuesday, February 18, 2020 at 5:48:37 PM UTC-8, BeeRich33 wrote:
>
> I get an error on the following query:
>
> a = DR[:company_t].where(Sequel.ilike(:company_name, '%Tru%') & (status: 
> 'active'))
>
>
> The error:
>
> syntax error, unexpected tLABEL
> ...mpany_name, '%Tru%') & (*status*: 'active'))
>
> status is a field name.  
>
> According to the Queries documentation page, this is apparently good.  
>
> Artist.where(Sequel.like(:name, 'Y%') & (Sequel[{b: 1}] | Sequel.~(c: 3)))
>
>
> Any insight as to why this is tripping?  It doesn't like my fieldname.  
>

It's not a Sequel error, you are providing invalid Ruby syntax.  You want

  & {status: 'active'}

instead of

  & (status: 'active')

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 sequel-talk+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sequel-talk/bed41ed5-8ef9-4f3b-aa36-a3c15afe084e%40googlegroups.com.

Re: Filtering & Subqueries

[Jeremy Evans]

On Tuesday, February 18, 2020 at 2:16:33 PM UTC-8, BeeRich33 wrote:
>
> I'm facing items like this:
>
> SELECT DISTINCT ON (listing_t.product_name) listing_t.product_name, 
> company_t.company_name, company_t.company_address, 
>   company_t.company_city, company_t.company_province, company_t.
> company_url, company_t.company_phone,
> FROM listing_t inner join s_ad_contracts_t on 
> s_ad_contracts_t.contract_company_id 
> = listing_t.product_company_id
>   inner join company_t on company_t.scid = s_ad_contracts_t.
> contract_company_id
> WHERE company_t.status = 'active'
>   AND listing_t.status = 'active'
>   AND s_ad_contracts_t.contract_status = 'active'
>   AND listing_t.product_name ILIKE '#{ submitted }'
> ORDER BY listing_t.product_name, company_t.company_name
> LIMIT 100
>

Assuming that submitted is a local variable in scope:

DB[:listing_t].
  distinct{listing_t[:product_name]}.
  select{[listing_t[:product_name], company_t[:company_name], 
company_t[:company_address], company_t[:company_city], 
company_t[:company_province], company_t[:company_url], 
company_t[:company_phone]]}.
  join(:s_ad_contacts, contract_company_id: :t_product_company_id).
  join(:company_t, scid: :contract_company_id).
  where{{company_t[:status]=>'active', 
listing_t[:status]=>'active',  s_ad_contracts_t[:contract_status]=>'active'}}.
  where{listing_t[:product_name].ilike(submitted.to_s)}.
  order{[listing_t[:product_name], company_t[:company_name]]}.
  limit(100)

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 sequel-talk+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sequel-talk/b9dc6b20-07bb-40d8-a90f-76e1320c4696%40googlegroups.com.