0

I have 3 tables, vendors, vendor_modelsand cameras. I am trying to get some values from db all of vendor_models but name from vendor too.

Each camera has a reference of vendor_model as model_id.

10 cameras can have one same vendor_model.

I am doing this query

SELECT v0."id", v0."vendor_id", v0."exid", v0."name", v0."username",
   v0."password", v0."jpg_url", v0."h264_url",  v0."mjpg_url", v0."mpeg4_url",
   v0."mobile_url", v0."lowres_url", v0."shape", v0."resolution", 
   v0."official_url", v0."more_info", v0."audio_url", v0."poe",
   v0."wifi", v0."upnp", v0."ptz", v0."infrared", v0."varifocal",
   v0."sd_card", v0."audio_io", v0."discontinued", v0."onvif",
   v0."psia", v0."channel", v0."created_at", v0."updated_at"
FROM "vendor_models" AS v0
INNER JOIN "vendors" AS v1 ON v0."vendor_id" = v1."id"
WHERE ((lower(v0."name") LIKE $1))
  OR (lower(v1."name") LIKE $2)
ORDER BY v1."name" ["%%", "%%"]

after getting all those values. I am doing another query. such as

SELECT c0."id", c0."owner_id", c0."model_id", c0."exid",
   c0."name", c0."timezone", c0."thumbnail_url",
   c0."is_online", c0."offline_reason", c0."is_public",
   c0."is_online_email_owner_notification", c0."alert_emails",
   c0."discoverable", c0."config", c0."mac_address",
   c0."location", c0."last_polled_at", c0."last_online_at",
   c0."created_at", c0."updated_at"
FROM "cameras" AS c0
WHERE (c0."model_id" = $1) [4]

which then give me the total number of cameras which are using this model.

my problem is I want to merge this query to in single one.

where I can get all the values of VendorModel as well as vendor. and also the count of cameras which are using each vendor model. which I am unable to do right now any help would be appreciable.

Update:

I have got here

SELECT vendor_models.id, vendor_models.name, count(cameras.id) as count 
  FROM "vendor_models" 
  INNER JOIN "vendors" ON "vendor_models"."vendor_id" = "vendors"."id" 
  INNER JOIN "cameras" ON "vendor_models"."id" = "cameras"."model_id"
  GROUP BY vendor_models.id, vendor_models.name
  ORDER BY count desc

But I cannot get vendor.name.. can you help?

Update:

SELECT vendor_models.id, vendor_models.name, v.name, count(cameras.id) as count 
  FROM vendor_models
  INNER JOIN vendors as v ON vendor_models.vendor_id = v.id 
  INNER JOIN cameras ON vendor_models.id = cameras.model_id
  GROUP BY vendor_models.id, vendor_models.name, v.name
  ORDER BY v.name asc

I got the final one. can you help to make it better?

  • 1
    Next time, please remember to start with your version of Postgres and table definitions (CREATE TABLE statements). – Erwin Brandstetter Feb 6 at 10:39
1

Still room for improvement. This is your last version, untangled, formatted and shortened with table aliases:

SELECT vm.id, v.name, vm.name, count(c.id) AS count 
FROM   vendors       v
JOIN   vendor_models vm ON vm.vendor_id = v.id 
JOIN   cameras       c  ON c.model_id = vm.id
GROUP  BY vm.id, v.name, vm.name
ORDER  BY v.name;

Remaining problems:

  • You have the name name twice in the result, which is unhelpful to say the least.
    (A shining example why non-descriptive names like "name" are bad names.)
  • Sort order not fully deterministic.
  • Not as fast as it could be.

I suggest:

SELECT vm.id
     , v.name AS vendor, vm.name AS model  -- column aliases to disambiguate
     , c.camera_count
FROM   vendors       v
JOIN   vendor_models vm ON vm.vendor_id = v.id 
JOIN  (
   SELECT model_id, count(*) AS camera_count  -- count(*) slightly faster
   FROM   cameras
   GROUP  BY 1           -- aggregate first, join later
   ) c ON c.model_id = vm.id
ORDER  BY v.name, vm.name, vm.id; -- add "id" if the names are not unique

If you count cameras per model first, you have far fewer join operations and don't have to aggregate outer columns at all. Should be faster.

count(*) is a bit faster than count(expression) (separate implementation) while doing the same in the absence of NULL values.

Since columns from the outer tables (vendors, vendor_models) are not aggregated at all now, you can put any of these columns in the SELECT list at will - including vm.*.

Related:

  • now can i get all vm.*? – Junaid Farooq Feb 6 at 10:43
  • @JunaidFarooq: Yes, that's trivial now, since outer columns are not aggregated at all. Just put it in the SELECT list. – Erwin Brandstetter Feb 6 at 10:46

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.