Avoid repeating the query

[amrutadotorg]

Hi. I'm trying to avoid repeating execution of the query. What are the best SQLPage practices to do that?

Thank you.

Example:

SELECT
 'map' AS component,
  'map' AS id,
  11 AS zoom,'' AS attribution,
    (meta_value->>'title')::text AS title,
    (meta_value->>'description')::text AS description_md,
    (meta_value->>'latitude')::numeric AS latitude,
    (meta_value->>'longitude')::numeric AS longitude
FROM
    post_meta
WHERE
    post_id = $id::int
    AND meta_key = 'geo'
    AND meta_value->>'latitude' IS NOT NULL
    AND meta_value->>'longitude' IS NOT NULL;

SELECT
    (meta_value->>'title')::text AS title,
    (meta_value->>'description')::text AS description_md,
    (meta_value->>'latitude')::numeric AS latitude,
    (meta_value->>'longitude')::numeric AS longitude,
    'users' AS icon,
    20 AS size
FROM
    post_meta
WHERE
    post_id = $id::int
    AND meta_key = 'geo'
    AND meta_value->>'latitude' IS NOT NULL
    AND meta_value->>'longitude' IS NOT NULL;

[lovasoa]

To avoid repeating the execution of similar queries in SQL, you can utilize Common Table Expressions (CTEs) or create views (either regular or materialized). Here’s a detailed explanation with examples on how to implement each approach:

Using Common Table Expressions (CTEs)

CTEs allow you to define a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. Here’s how you can rewrite your queries using CTEs to avoid redundancy:

WITH geo_data AS (
    SELECT
        (meta_value->>'title')::text AS title,
        (meta_value->>'description')::text AS description_md,
        (meta_value->>'latitude')::numeric AS latitude,
        (meta_value->>'longitude')::numeric AS longitude
    FROM
        post_meta
    WHERE
        post_id = $id::int
        AND meta_key = 'geo'
        AND meta_value->>'latitude' IS NOT NULL
        AND meta_value->>'longitude' IS NOT NULL
)

-- First query
SELECT
    'map' AS component,
    'map' AS id,
    11 AS zoom,
    '' AS attribution,
    title,
    description_md,
    latitude,
    longitude
FROM geo_data

UNION ALL

-- Second query
SELECT
    title,
    description_md,
    latitude,
    longitude,
    'users' AS icon,
    20 AS size
FROM geo_data;

Using Views

Views are virtual tables that represent the result of a query. They can be used to simplify complex queries or to encapsulate repeated logic. Here’s how to create a view for your case:

CREATE VIEW geo_data AS
SELECT
    (meta_value->>'title')::text AS title,
    (meta_value->>'description')::text AS description_md,
    (meta_value->>'latitude')::numeric AS latitude,
    (meta_value->>'longitude')::numeric AS longitude
FROM
    post_meta
WHERE
    post_id = $id::int
    AND meta_key = 'geo'
    AND meta_value->>'latitude' IS NOT NULL
    AND meta_value->>'longitude' IS NOT NULL;

You can then query this view as follows:

-- First query
SELECT
    'map' AS component,
    'map' AS id,
    11 AS zoom,
    '' AS attribution,
    title,
    description_md,
    latitude,
    longitude
FROM geo_data;

-- Second query
SELECT
    title,
    description_md,
    latitude,
    longitude,
    'users' AS icon,
    20 AS size
FROM geo_data;

Using Materialized Views

Materialized views store the result of a query physically and must be refreshed manually or at specified intervals. This can be useful for performance optimization if the data does not change frequently.

CREATE MATERIALIZED VIEW geo_data AS
SELECT
    (meta_value->>'title')::text AS title,
    (meta_value->>'description')::text AS description_md,
    (meta_value->>'latitude')::numeric AS latitude,
    (meta_value->>'longitude')::numeric AS longitude
FROM
    post_meta
WHERE
    post_id = $id::int
    AND meta_key = 'geo'
    AND meta_value->>'latitude' IS NOT NULL
    AND meta_value->>'longitude' IS NOT NULL;

-- Refresh the materialized view as needed
REFRESH MATERIALIZED VIEW geo_data;

You can query the materialized view in the same way as a regular view:

-- First query
SELECT
    'map' AS component,
    'map' AS id,
    11 AS zoom,
    '' AS attribution,
    title,
    description_md,
    latitude,
    longitude
FROM geo_data;

-- Second query
SELECT
    title,
    description_md,
    latitude,
    longitude,
    'users' AS icon,
    20 AS size
FROM geo_data;

Documentation Links

• Common Table Expressions (CTEs) in PostgreSQL
• Views in PostgreSQL
• Materialized Views in PostgreSQL

Using CTEs or views (regular or materialized) can greatly simplify your SQL queries and avoid repeated execution of the same query logic. This not only makes your code cleaner but can also improve performance and maintainability.

[amrutadotorg]

Hi, thank you so much for the explanations. I tried CTE from your example but I'm getting
relation "geo_data" does not exist (error from the 2nd select)

The code below works (only one select).

WITH soundcloud_data AS (
    SELECT
        meta_value::jsonb AS audio_data
    FROM
        post_meta
    WHERE
        post_id = $id::int AND meta_key = 'soundcloud'
)
SELECT
    audio_data->>'url' AS embed,
    'autoplay' AS allow,
    'iframe' AS embed_mode,
    '300' AS height,
    'audio'   as id
FROM
    soundcloud_data
WHERE
    audio_data->>'url' IS NOT NULL;

Is this something you can fix in SQLPage?

[lovasoa]

Sorry, my answer above was incorrect, I edited it.

UNION ALL between the two queries was missing

[amrutadotorg]

Thank you, I had modify it a little bit because I was getting error returned from database: each UNION query must have the same number of columns

WITH geo_data AS (
    SELECT
        (meta_value->>'title')::text AS title,
        (meta_value->>'description')::text AS description_md,
        (meta_value->>'latitude')::numeric AS latitude,
        (meta_value->>'longitude')::numeric AS longitude
    FROM
        post_meta
    WHERE
        post_id = $id::int
        AND meta_key = 'geo'
        AND meta_value->>'latitude' IS NOT NULL
        AND meta_value->>'longitude' IS NOT NULL
)
SELECT
    'map' AS component,
    'map' AS id,
    11 AS zoom,
    '' AS attribution,
    title,
    description_md,
    latitude,
    longitude,
    NULL AS icon,
    NULL AS size
FROM
    geo_data
UNION ALL
SELECT
    NULL AS component,
    NULL AS id,
    NULL AS zoom,
    NULL AS attribution,
    title,
    description_md,
    latitude,
    longitude,
    'users' AS icon,
    20 AS size
FROM
    geo_data;