PostgreSQL - join tables using pattern matching

Question

I have two tables and need to join them using two columns that are similar.

The first table is called articles has a column called 'slug' with slug lines for articles, ex: 'trump-fails-yet-again.'

The second table is called log and has a column called path with the url path for the articles, ex: '/articles/trump-fails-yet-again/'

Here is my search query:

"SELECT articles.title, count(*) as num FROM articles, log WHERE articles.slug LIKE CONCAT('%',log.path) GROUP BY articles.title;"

This returns nothing but brackets, []

I have also tried:

"SELECT articles.title, count(*) as num FROM articles JOIN log ON articles.slug SIMILAR TO CONCAT('%',log.path) GROUP BY articles.title;"

That returns a DataError: invalid regular expression: quantifier operand invalid

Any help is greatly appreciated!

I'm confused when you pay pattern matching, you mean you just want to replace spaces with '-' on the join condition? — Evan Carroll
– Evan Carroll, Commented Aug 8, 2017 at 0:02
I need to match the slug line to the log path after /articles/ — JTP709
– JTP709, Commented Aug 8, 2017 at 1:49

Gordon Linoff · Accepted Answer · 2017-08-08 01:50:53Z

2

You have a slash at the end of the path. How about this?

SELECT a.title, count(*) as num
FROM articles a JOIN
     log l
     ON a.path LIKE '%' || l.slug || '%'
GROUP BY a.title;

You should also learn to use proper, explicit JOIN syntax. Never use commas in the FROM clause.

edited Aug 8, 2017 at 1:50

answered Aug 7, 2017 at 23:51

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JTP709 Over a year ago

This returns no errors but still only returns a table with 0 rows.

Gordon Linoff Over a year ago

@JTP709 . . . The logic was backwards. The slug should be in the pattern.

Muataz Mahmoud · Accepted Answer · 2017-12-04 17:08:49Z

2

try this:

` select articles.title, count(*) as views from articles
  join log on articles.slug ~~ ('%' || articles.slug || '%') 
  group by articles.title;`

answered Dec 4, 2017 at 17:08

Muataz Mahmoud

1151 gold badge1 silver badge9 bronze badges

Comments

Evan Carroll · Accepted Answer · 2017-08-08 01:53:47Z

0

Because there is a 1:1 function with this you can do this

SELECT articles.title, count(*) as num
FROM articles
JOIN log ON articles.slug = '/articles/' || articles.slug || '/'
GROUP BY articles.title;

Or even better

CREATE FUNCTION slug_to_article_path( slug text )
RETURNS text AS
$$
  SELECT '/articles/' || slug || '/';
$$ LANGUAGE sql
IMMUTABLE;

SELECT articles.title, count(*) as num
FROM articles
JOIN log ON articles.slug = slug_to_article_path(articles.slug)
GROUP BY articles.title;

answered Aug 8, 2017 at 1:53

Evan Carroll

1

Collectives™ on Stack Overflow

PostgreSQL - join tables using pattern matching

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related