CODEWITHSUNDEEP
sqlpostgresqljoinpattern-matching
JTP709
JTP709

Reputation: 130

PostgreSQL - join tables using pattern matching

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!

Upvotes: 2

Views: 3244

Answers (3)

Muataz Mahmoud
Muataz Mahmoud

Reputation: 115

try this:

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

Upvotes: 2

Evan Carroll
Evan Carroll

Reputation: 1

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;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

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.

Upvotes: 1

Related Questions