Selecting pages by path in MySQL/SQLite databases

Question

Hi all, I am storing pages for websites in a 'pages' database table, they are referenced by their path (i.e. 'articles/my-first-blog-post'), I need to select all the children of a particular page, but without selecting grandchildren.

So if I run:

SELECT * FROM pages WHERE path LIKE 'articles%'

I'll get pages with the following paths:

articles/one
articles/two
articles/two/more
articles/three
articles/three/more/even-more

I need to filter them (in the query) to just:

articles/one
articles/two
articles/three

Is there anyway to do something like:

SELECT * FROM pages WHERE path LIKE 'articles%' AND path NOT LIKE 'articles%/%'

Any ideas? Cheers.

Answer 1

If your files have file extensions this will work:

   SELECT * FROM pages 
    WHERE path LIKE 'articles%' 
      AND SUBSTRING_INDEX(path,'/',2) LIKE '%.file_extension';

otherwise:

   SELECT * FROM pages 
    WHERE path LIKE 'articles%' 
      AND SUBSTRING_INDEX(path,'/',2)=SUBSTRING_INDEX(path,'/',3)=;

Answer 2

You can use regular expressions for that. The keyword REGEXP works both for mysql and sqlite:

... WHERE path REGEXP '^articles/[^/]+'

Answer 3

SELECT * FROM pages WHERE path LIKE "%/%" AND path NOT LIKE "%/%/%";

works for me at least.

Answer 4

Hi

Using regular expressions (LIKE clause or REGEXP) may incur severe performance problems in SQLite because they require full table scan. Read more about this in http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html.

You can use inequality operators (like < and >) to increase performance (if the relevant column has an index).

Liron Levi (author of SQLite Compare diff/merge tool)