0

I ran into an issue on trying to optimise the following two query into 1 single query.

Result 1:

select * 
from sip_trunks 
where resporg_account_id_id=62 
and identifier = (select action 
                  from sip_trunk_numbers 
                  where number='xxxxxxxxxx' 
                  and resporg_id=80 limit 1) 
limit 1

Result 2:

select * 
from sip_trunks 
where resporg_account_id_id=62 
and sip_trunks.default=true 
limit 1

Final Output = Result 1 + Result 2

Thus far I have managed to achieve this.

Note: I can't perform a UNION since I'm not sure whether RESULT 1 would yield any result or not (hence the flag matcher is used in below query. other than checking the rows returned) I know that Result 2 would always be there.

select *, 
        (case 
           when identifier=(select action from sip_trunk_numbers where number='xxxxxxxxxx' and resporg_id=80 limit 1) 
             then 1
           when sip_trunks.default=true 
             then 2
           else 3
         end) as matcher
from sip_trunks 
where resporg_account_id_id=62 
order by matcher asc 
limit 2;

Now I'm stuck.

a: I can't group the result based on the matcher as Postgres complain the '*' is not used in the group.

But First I'm not sure how bad is the subquery is (select action from sip_trunk_numbers where number='xxxxxxxxxx' and resporg_id=80 limit 1)

So does using a subquery as column would make it run for all the rows or it would run for rows for which the where conditions matched.

In other word does the subquery would run for all rows of sip_trunks or it would only run for rows for which the resporg_account_id_id=62 matches.

  • 1
    Of course you can UNION the two results. – a_horse_with_no_name Aug 14 at 6:58
  • yes, having to adjust the application code every time we change the limit thing of either of two queries is a bottleneck here.. – Noobie Aug 14 at 7:05
1

Why don't you use an OR condition?

select * 
from sip_trunks 
where resporg_account_id_id = 62 
  and (   sip_trunks.default = true 
       or identifier = (select action 
                        from sip_trunk_numbers 
                        where number = 'xxxxxxxxxx' 
                        and resporg_id = 80 
                        limit 1))

limit 1

If you need to know which condition matched, you can use a UNION:

select *
from (
  select st.*, 1 as source
  from sip_trunks st
  where resporg_account_id_id = 62 
    and identifier = (select action 
                      from sip_trunk_numbers 
                      where number = 'xxxxxxxxxx' 
                        and resporg_id = 80 
                      limit 1) 
  limit 1

  union all

  select st.*, 2 as source
  from sip_trunks st
  where resporg_account_id_id = 62 
    and sip_trunks.default = true 
  limit 1  
) 
order by source 
limit 1

If source equals one in the result the first query matched, otherwise the second.


Alternatively you could use an outer join:

select st.*, 
       stn.action is not null as identifier_matched
from sip_trunks st
  left join (
    select action 
    from sip_trunk_numbers 
    where number = 'xxxxxxxxxx' 
      and resporg_id = 80 
    limit 1  
  ) stn on stn.action = st.identifier
where resporg_account_id_id = 62 
  and (stn.action is not null or sip_trunks.default = true)
limit 1

Note that limit without an order by usually makes no sense.

  • the thing is I would have no clue with the returned result whether the identifier was a match or not. – Noobie Aug 14 at 7:12
  • @Noobie see my edit – a_horse_with_no_name Aug 14 at 7:24
  • @ypercubeᵀᴹ: thanks, corrected. – a_horse_with_no_name Aug 14 at 7:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.