How to use variables in psql LIKE query regular expression

Question

I have a psql database with two tables that I want to join for a query. Table 1 (we can called Articles) has two columns:

• slug - which is the name of the article path in the website

  • example 1 - 'my-article-1'
  • example 2 - 'my-article-2'

• title - the actual title of the article

  • example 1 - 'My first article'
  • example 2 - 'My second article'

Table 2 (we will call Log) has two columns which together represents request responses sent back from the server:

• url - which is the url path requested by GET

  • example 1 - '/articles/my-article1'
  • example 2 - '/articles/my-article2'

• status - which is the status code send back from server

  • example - '200 OK'

I want to return the number of times that a specific article has been requested and returned a '200 OK' status code.

My Problem: I want to match the Articles.slug variable to the Log.url variable in my where clause, but I don't know how to use variables in regular expressions. Right now I'm just working on matching the slug to the path, here is my code so far:

SELECT articles.title, articles.slug, log.path
FROM articles, log
WHERE log.path like '/articles/' + articles.slug
and log.status = '200 OK'

I know that my WHERE clause is incorrect, that's what I'm looking for. How do I match the full log.path string to a concatenated string with a variable, articles.slug.

Sorry if any of this is unclear or looks stupid, I'm learning SQL for the first time <3

Answer 1

I figured out a solution that doesn't exactly answer my question, but here's how I got around it.

select articles.title, count(*)
from articles, log
where substring(log.path,10,100) = articles.slug
and log.status = '200 OK'
group by articles.title