CODEWITHSUNDEEP
sqlpostgresql
user1575921
user1575921

Reputation: 1088

select or count from join table

I wanna count gallery_id = 1 media, exist in media_action rows,
before I was use below query, I have select all rows in gallery_media then loop, wondering any better? 

// select all gallery_media rows
var dbQuery = `SELECT *  
  FROM "gallery_media" WHERE gallery_id = $1`;
var dbParams = [galleryId];
var dbQueryR = yield Promise.resolve( queryPromise(dbClient, dbQuery, dbParams) );

// loop
var total;
for (var i = 0; i < dbQueryR.rows.length; i++) {
  var mediaId = dbQueryR.rows[i].media_id;
  // select count media_action where media_id = mediaId 
  // and total++; 
  var dbQuery = `SELECT count(*)
    AS total_row_count 
    FROM "media_action" WHERE media_id = $1`;
}

// below example data final result get total = 3
// if add where type = 0 get total = 2

data

gallery_media    
gallery_id | media_id  
1 | 1  
1 | 2  
1 | 3  

media_action     
media_id | type  
1 | 0  
3 | 0  
3 | 1

table

CREATE TABLE gallery_media
(
  id serial NOT NULL,
  gallery_id integer NOT NULL,
  media_id integer NOT NULL,
...

CREATE TABLE media_action
(
  id serial NOT NULL,
  media_id integer NOT NULL,
  type integer NOT NULL
...

Upvotes: 1

Views: 151

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656391

Looks like a plain join - with an optionally added WHERE condition:

SELECT count(*) AS total 
FROM   gallery_media g
JOIN   media_action  m USING (media_id)
WHERE  g.gallery_id = $1
-- AND m.type = 0  -- optional

Details in the manual.

Upvotes: 2

Related Questions