DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’»

Jeremy Friesen for The DEV Team

Posted on • Originally published at takeonrules.com on

SQL Proof of Concept for Collating Different Article Feed Selection Criteria for DEV.to

Scratching Down Some Ideas to Share

I previously wrote about the Diving into Dev’s Relevancy Feed Builder. And what follows is a quick proof of concept regarding a possible feature for the DEV.to feed.

What we want is to run an experiment in which we inter-weave two different sets of articles to create the relevancy feed. Some constraints are:

  1. There are two sets of articles.
  2. We don’t want duplicate articles.
  3. We want a primary set and a secondary set.
  4. We want the secondary set to be interspersed within the primary set.
  5. How we select what is in each set is it’s own unique problem.

Proof of Concept SQL and Result Set

What follows is a quick Postgresql query that is database agnostic. Basically, can I setup a collation of two sets of data. There are some refinements to how I collate, but I’m presently satisfied with the bones of the query.

WITH integers AS (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY '1') AS collation_order,
    'integers' AS table_source,
    ,*
    FROM generate_series(1,12)
), threes AS (
  SELECT
    (ROW_NUMBER() OVER (PARTITION BY '1') * 0.75 + 0.1) AS collation_order,
    'threes' AS table_source,
    ,*
    FROM generate_series(3,27,3)
)
    (
      (SELECT * FROM integers)
      UNION
      (SELECT * FROM threes
    WHERE generate_series NOT IN (
      SELECT generate_series FROM integers))
    ) ORDER BY collation_order

Enter fullscreen mode Exit fullscreen mode

The results are as follows:


SQL Results of Collation of Two Datasets from Common Domains
collation_order table_source generate_series
1.0 integers 1
2.0 integers 2
3.0 integers 3
3.85 threes 15
4.0 integers 4
4.6 threes 18
5.0 integers 5
5.35 threes 21
6.0 integers 6
6.1 threes 24
6.85 threes 27
7.0 integers 7
8.0 integers 8
9.0 integers 9
10.0 integers 10
11.0 integers 11
12.0 integers 12

Conclusion

I’m not yet certain if this is something I’ll incorporate into the possible Feed Variants, but I wanted to put this out there.

Top comments (0)

πŸ‘‹ Welcome new DEV members in our Welcome Thread

Say hello to the newest members of DEV.