2

PostgreSQL 9.5.6

I have two tables - t1, t2.

t1 has a title column with titles separated by spaces.

t1.title
--------
hello kitty workd
gone wind forever

t2 has a path column with paths separated by dashes and prefixed 'path/'

t2.path
-------
path/hello-kitty-world
path/gone-wind-forever

GOAL: FOR EACH t1.title COUNT THE NUMBER OF TIMES IT SHOWS UP IN t2

Course of action: parse t2.path so that it looks like t1.title in order to do a left join and count**

I'm very new to this, if you have a better approach altogether I would appreciate any suggestions

So far I have:

select t1.title, count(t2.path) as num 
from t1 left join t2 on t1.title = substring(log.path from 6) 
where t1.title like '%'||split_part(substring(log.path from 6),'-',1)||'%' 
group by articles.title; 
2
  • Thank you! btw, do you know how I can shorten a multi-word string to say 3 words? For all the substring() functions I need to know the index position of the char where I want to start extraction, but the strings I'm working with are varying length.
    – S.Ro
    May 27, 2017 at 4:40
  • 2
    @S.Ro Although shortening a multi-word string to 3 words may be related to the question you've asked here, it nevertheless appears to be an entirely different issue. Please don't make a mess out of this thread by raising several distinct issues at once. That multi-word problem looks to me like a perfectly valid question in itself, so, if you need help there, please post a new question, thank you!
    – Andriy M
    May 27, 2017 at 16:36

2 Answers 2

2

@ypercube said in question comments:

You don't need to split the strings. If there is always a single space between word, you can replace space with - and then join: ON 'path/' || replace(t1.title, ' ', '-') = t2.path

Or the reverse: ON t1.title = replace(substring(t2.path, 6), '-', '')

You can use replace() function in the ON clause to compare both strings and get the desired result:

select     title, path
from       t1
inner join t2
on         'path/' || replace(t1.title, ' ', '-') = t2.path;
title             | path                  
:---------------- | :---------------------
gone wind forever | path/gone-wind-forever
select     title, path
from       t1
inner join t2
on         t1.title = replace(substring(t2.path, 6), '-', ' ');
title             | path                  
:---------------- | :---------------------
gone wind forever | path/gone-wind-forever

dbfiddle here

0

Paths as a function of title is perfectly fine. However, using them in a join condition is an anti-pattern. Instead, create a function to generate them.

CREATE FUNCTION path_from_title ( IN title text, OUT path text )
AS $$
  SELECT 'path/' || replace($1, ' ', '-');
$$ LANGUAGE 'sql'
STRICT IMMUTABLE;

Now either join on the natural key (title) which makes things simple, or store a fkey to the title table.

If that doesn't make sense, it's ok to have data as a function of an immutable natural key. Path is f(title). It's weird though to then have to create g(f(title)) as an inverse of f(title) just to do a join.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.