postgresql - Fetching distinct rows from multiple joins SQL -


i have 1 main table called deliveries , has 1 many relationship deliveries_languages dl, deliveries_markets dm , deliveries_tags dt having delivery_id foreign key. these 3 tables have 1 one relation languages , markets , tags respectively. additionaly, deliveries, table have 1 one relation companies , have company_is foreign key. following query have written: 

select deliveries.*, languages.display_name, markets.default_name, tags.default_name, companies.name deliveries join deliveries_languages dl on dl.delivery_id = deliveries.id  join deliveries_markets dm on dm.delivery_id = deliveries.id  join deliveries_tags dt on dt.delivery_id = deliveries.id  join languages on languages.id = dl.language_id  join markets on markets.id = dm.market_id  join tags on tags.id = dt.tag_id  join companies on companies.id = deliveries.company_id   deliveries.name ilike '%new%' ,  deliveries.created_by = '5f331347-fb58-4f63-bcf0-702f132f97c5' ,  deliveries.deleted_at null  limit 10

here getting redundant delivery_ids because each delivery_id there multiple languages, markets , tags. want use limit on distinct delivery_ids. so, limit 10 should not give me 10 records above join query 10 records there distinct delivery_id (deliveries.id). can use derived table concept here not sure how can that. can please me resolve issue.

in postgres, can use distinct on:

select distinct on (d.id) d.*, l.display_name, m.default_name, t.default_name, c.name deliveries d join      deliveries_languages dl      on dl.delivery_id = d.id join      deliveries_markets dm      on dm.delivery_id = d.id join      deliveries_tags dt      on dt.delivery_id = d.id join      languages l      on l.id = dl.language_id join      markets m      on m.id = dm.market_id join      tags      on t.id = dt.tag_id join      companies c      on  c.id = d.company_id  d.name ilike '%new%' ,        d.created_by = '5f331347-fb58-4f63-bcf0-702f132f97c5' ,        d.deleted_at null  order d.id limit 10;

for larger amounts of data, can inefficient. multiple junction tables result in cartesian products when used this. however, smallish amount of data, should solve problem.


Comments

Post a Comment

Popular posts from this blog

resizing Telegram inline keyboard -

telegram's inline keyboard great feature lots of different use cases. inline buttons added list of items this: inline_keyboard = [[inlinekeyboardbutton(text="button", callback_data="button"), inlinekeyboardbutton(text="reset",callback_data="reset")]] inline_keyboard_markup = inlinekeyboardmarkup(inline_keyboard) update.message.reply_text("hi", reply_markup=inline_keyboard_markup) the above code adds 2 buttons each half width of chat screen. i know the normal keyboard button there resize_keyboard parameter somehow can used. my question is there way resize inline buttons ? example make full width or quarter width. unfortunately, can't now. :( you can suggest @botsupport , might add feature next version.
Read more

command line - How can a Python program background itself? -

consider following python command-line program. doesn't work because incomplete, think illustrates problem: #!/usr/bin/python3 import multiprocessing pool = multiprocessing.pool() task in expensive_iterator_with_user_interaction(): pool.call_async(expensive_computation, (task,)) pool.close() print("dear user, may press ctrl-z , bg script.") pool.join() thus, problem scripts needs user input while it's launching expensive parallel processes. there moment when user interaction not needed anymore. then, ask user background process shell actions. however, can achieved program itself? (side note: core of problem entanglement of user input firing “expensive computations”. therefore, cannot separate both in 2 scripts, , letting 1 spawning other daemon.) the command screen may you. use screen -l create new session , run application. after inputting data, can press " ctrl-a " , press " d " on keyboard detach session. now...
Read more

php - "cURL error 28: Resolving timed out" on Wordpress on Azure App Service on Linux -

Image
my wordpress installation on azure app service on linux using custom docker container has slow response times. pages take around 20-40 seconds load. i have troubleshooting plugin installed indicates problem "curl error 28: resolving timed out after n milliseconds" when making requests following urls https://api.wordpress.org/core/version-check/1.7/ http://api.wordpress.org/core/version-check/1.7/ https://api.wordpress.org/plugins/update-check/1.1/ http://api.wordpress.org/plugins/update-check/1.1/ https://api.wordpress.org/themes/update-check/1.1/ http://api.wordpress.org/themes/update-check/1.1/ curl works fine on scm-site command line. example works ok. curl -x post http://api.wordpress.org/core/version-check/1.7/ edit if ssh container , run php code, works fine. <?php $url = 'http://api.wordpress.org/core/version-check/1.7/'; $fields = array( 'version' => urlencode('4.8.1'), 'php' => urlencode(...
Read more