Clever Geek Handbook

Selecting pages by path in MySQL / SQLite databases

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

So, if I run:

SELECT * FROM pages WHERE path LIKE 'articles%'

I will get pages with the following paths:

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

I only need to filter them (in the request):

articles / one
articles / two
articles / three

Is there a way to do something like:

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

Any ideas? Greetings.

+2


source to share


4 answers


You can use regular expressions for this. The keyword REGEXP

works for both mysql and sqlite :



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


source


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)=;
+2


source


Using regular expressions (LIKE or REGEXP clause) can lead to severe performance problems in SQLite as they require a full table scan. Read more about this at http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html .

You can use inequality operators (such as <and>) to improve performance (if the corresponding column has an index).

+1


source 


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

works for me at least.

0


source






More articles:


All Articles