72788

WHERE … IN condition and multiple columns in subquery

Question:

Is it please possible to rewrite the SQL query

SELECT DISTINCT ON (uid) uid, female, given, photo, place FROM words_social WHERE uid IN (SELECT player1 FROM games) OR uid IN (SELECT player2 FROM games) ORDER BY uid, stamp DESC

where first column player1 is fetched in a subquery and then column player2 is fetched from the same table?

I've searched around and it seems that a JOIN should be used here instead of the 2 subqueries, but can not figure out exactly how.

Just to give more context - below are the 2 tables in question

CREATE TABLE words_social ( sid varchar(255) NOT NULL, social integer NOT NULL CHECK (0 <= social AND social <= 6), female integer NOT NULL CHECK (female = 0 OR female = 1), given varchar(255) NOT NULL CHECK (given ~ '\S'), family varchar(255), photo varchar(255) CHECK (photo ~* '^https?://...'), place varchar(255), stamp integer NOT NULL, /* Only the most recent stamp is used */ uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE, PRIMARY KEY(sid, social) ); CREATE TABLE words_games ( gid SERIAL PRIMARY KEY, created timestamptz NOT NULL, finished timestamptz, player1 integer REFERENCES words_users(uid) ON DELETE CASCADE NOT NULL, player2 integer REFERENCES words_users(uid) ON DELETE CASCADE, played1 timestamptz, played2 timestamptz, mid integer /* REFERENCES words_moves */, score1 integer NOT NULL CHECK (score1 >= 0), score2 integer NOT NULL CHECK (score2 >= 0), hand1 varchar[7] NOT NULL, hand2 varchar[7] NOT NULL, pile varchar[116] NOT NULL, letters varchar[15][15] NOT NULL, values integer[15][15] NOT NULL, bid integer NOT NULL REFERENCES words_boards ON DELETE CASCADE );

and the actual CTE query, which works well, but I would like to optimize it:

CREATE OR REPLACE FUNCTION words_get_games(in_uid integer) RETURNS TABLE ( out_gid integer, out_created integer, out_finished integer, out_player1 integer, out_player2 integer, out_played1 integer, out_played2 integer, out_score1 integer, out_score2 integer, out_hand1 text, out_hand2 text, out_letters varchar[15][15], out_values integer[15][15], out_bid integer, out_last_tiles jsonb, out_last_score integer, out_female1 integer, out_female2 integer, out_given1 varchar, out_given2 varchar, out_photo1 varchar, out_photo2 varchar, out_place1 varchar, out_place2 varchar ) AS $func$ WITH games AS ( SELECT g.gid, EXTRACT(EPOCH FROM g.created)::int AS created, EXTRACT(EPOCH FROM g.finished)::int AS finished, g.player1, g.player2, -- can be NULL EXTRACT(EPOCH FROM g.played1)::int AS played1, EXTRACT(EPOCH FROM g.played2)::int AS played2, g.score1, g.score2, ARRAY_TO_STRING(g.hand1, '') AS hand1, REGEXP_REPLACE(ARRAY_TO_STRING(g.hand2, ''), '.', '?', 'g') AS hand2, g.letters, g.values, g.bid, m.tiles AS last_tiles, m.score AS last_score FROM words_games g LEFT JOIN words_moves m USING(mid) WHERE g.player1 = in_uid AND (g.finished IS NULL OR g.finished > CURRENT_TIMESTAMP - INTERVAL '1 day') UNION SELECT g.gid, EXTRACT(EPOCH FROM g.created)::int AS created, EXTRACT(EPOCH FROM g.finished)::int AS finished, g.player2 AS player1, g.player1 AS player2, -- can not be NULL EXTRACT(EPOCH FROM g.played2)::int AS played1, EXTRACT(EPOCH FROM g.played1)::int AS played2, g.score2 AS score1, g.score1 AS score2, ARRAY_TO_STRING(g.hand2, '') AS hand1, REGEXP_REPLACE(ARRAY_TO_STRING(g.hand1, ''), '.', '?', 'g') AS hand2, g.letters, g.values, g.bid, m.tiles AS last_tiles, m.score AS last_score FROM words_games g LEFT JOIN words_moves m USING(mid) WHERE g.player2 = in_uid AND (g.finished IS NULL OR g.finished > CURRENT_TIMESTAMP - INTERVAL '1 day') ), social AS ( SELECT DISTINCT ON (uid) uid, female, given, photo, place FROM words_social WHERE uid IN (SELECT player1 FROM games) /* How to optimize? */ OR uid IN (SELECT player2 FROM games) ORDER BY uid, stamp DESC ) SELECT g.gid, g.created, g.finished, g.player1, g.player2, g.played1, g.played2, g.score1, g.score2, g.hand1, g.hand2, g.letters, g.values, g.bid, g.last_tiles, g.last_score, s1.female, s2.female, s1.given, s2.given, s1.photo, s2.photo, s1.place, s2.place FROM games g LEFT OUTER JOIN social s1 ON g.player1 = s1.uid LEFT OUTER JOIN social s2 ON g.player2 = s2.uid; $func$ LANGUAGE sql;

Answer1:

Just to prove that you don't need the CTEs, here is your query rewritten without them.

<ul><li>had to guess some table structures, because the question was incomplete</li> <li>a prepared statement instead of a function(the subsynstax is similiar)</li> <li>rewrote the player1<-->player2 duplication; this can easily be handled via a CASE expression or the like </li> <li>rewrote the <em>most recent social record</em> using not exists (could also be done via a row_number() OVER (partition by uid ORDER BY tstamp DESC) rn ... where rn=1</li> <li>removed some decorative FD fields</li> </ul><hr />PREPARE rewrite2(integer) AS SELECT g.gid , EXTRACT(EPOCH FROM g.created)::int AS created , EXTRACT(EPOCH FROM g.finished)::int AS finished , g.player1 , g.player2 -- can be NULL , EXTRACT(EPOCH FROM g.played1)::int AS played1 , EXTRACT(EPOCH FROM g.played2)::int AS played2 , g.score1 , g.score2 , ARRAY_TO_STRING(g.hand1, '') AS hand1 , REGEXP_REPLACE(ARRAY_TO_STRING(g.hand2, ''), '.', '?', 'g') AS hand2 , g.letters , g.values , g.bid , m.tiles AS last_tiles , m.score AS last_score , s1.female AS female1 , s1.given AS given1 , s2.female AS female2 , s2.given AS given2 FROM words_games g LEFT JOIN words_moves m USING(mid) LEFT JOIN words_social s1 ON s1.uid = g.player1 AND NOT EXISTS( SELECT * FROM words_social nx WHERE s1.uid = nx.uid AND nx.stamp > s1.stamp) LEFT JOIN words_social s2 ON s2.uid = g.player2 AND NOT EXISTS( SELECT * FROM words_social nx WHERE s2.uid = nx.uid AND nx.stamp > s2.stamp) WHERE (g.player1 = $1 OR g.player2 = $1) AND (g.finished IS NULL OR g.finished > CURRENT_TIMESTAMP - INTERVAL '1 day') ; EXPLAIN EXECUTE rewrite2(1);

Answer2:

SELECT DISTINCT ON (uid) uid, female, given, photo, place FROM words_social ws INNER JOIN games g ON ws.uid = g.player1 OR ws.uid = g.player2 ORDER BY ws.uid, stamp DESC

Answer3:

Use EXISTS when IN doesn't suffice.

SELECT DISTINCT ON (uid) uid, female, given, photo, place FROM words_social ws WHERE EXISTS ( SELECT * FROM games g WHERE ws.uid IN (g.player1, g.player2) ) ORDER BY uid, stamp DESC

Recommend

  • How to use for loop to drastically reduce code length?
  • Matching data from one data frame to another
  • Get difference in minutes between times with timezone
  • Two by two matching between dataframes in r
  • How to get the frequent value output inside the div?
  • Getting a weighted average in R when joining two tables
  • How to sort numbers in ascending order in a csv file made from python
  • Interval (days) in PostgreSQL with two parameters
  • Get game_id of a players MAX(score) in sql
  • Function to update a status flag for validity of other column?
  • Adding sum of current_timestamp and days column in Postgres
  • Receiving an error when requesting members for an array
  • Array.prototype.sort.apply( someArray, args ) vs someArray.sort.apply( someArray, args )
  • How to add multiple columns in Apache Spark
  • sorting three number in a specific order
  • How to Count the Number of a Specific Character in a Cell with Excel VBA
  • Can someone please explain how this implementation of bucket sort works?
  • Where to put clearQueue in jQuery code
  • SQL - count occurrences of gender
  • Wait for .each() .getJSON request to finish before executing a callback
  • Do query loads all the data in memory
  • Apache 2.4 - remove | delete | uninstall
  • How to delete a row from a dynamic generate table using jquery?
  • Python: how to group similar lists together in a list of lists?
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • Codeigniter doesn't let me update entry, because some fields must be unique
  • Free memory of cv::Mat loaded using FileStorage API
  • Trying to get generic when generic is not available
  • Getting Messege Twice Using IMvxMessenger
  • Change div Background jquery
  • How to get Windows thread pool to call class member function?
  • Bitwise OR returns boolean when one of operands is nil
  • XCode 8, some methods disappeared ? ex: layoutAttributesClass() -> AnyClass
  • Easiest way to encapsulate a HTML5 webpage into an android app?
  • Busy indicator not showing up in wpf window [duplicate]
  • costura.fody for a dll that references another dll
  • Observable and ngFor in Angular 2
  • How to Embed XSL into XML
  • UserPrincipal.Current returns apppool on IIS
  • Conditional In-Line CSS for IE and Others?