php - Symfony Sonata SEO custom sql query with LIKE


I am using Sonata SEO on a Symfony 4 project.

sonata-project/seo-bundle: ^2.7

In myconfig/packages/sonata_seo.yaml file I have defined a custom SQL query to generate the sitemap with like so:

        - { connection: doctrine.dbal.default_connection, route: catch_all, parameters: {path: null}, query: "SELECT `path`, `updated_at` AS `lastmod`, 'weekly' AS `changefreq`, '0.5' AS `priority` FROM `pages` WHERE `published` = 1 AND `path` NOT LIKE('checkout/%') AND `path` NOT LIKE('bills/%%')" }

For easier reading (yaml forces me to have this in one line) here is the query:

SELECT `path`, `updated_at` AS `lastmod`, 'weekly' AS `changefreq`, '0.5' AS `priority` 
FROM `pages` 
WHERE `published` = 1 AND `path` NOT LIKE('checkout/%') AND `path` NOT LIKE('bills/%')

Basically I am usingWHERE NOT LIKE to exclude certain pages from the sitemap being generated.

If I run the query in mysql directly it works as expected.

If I run it via the sonata seo command line utility like so:

php bin/console sonata:seo:sitemap '/path/to/sitemap/folder' 'https://dev.test'

I get this error:

The parameter "\') AND `path` NOT LIKE(\'bill/" must be defined.

I've tried to escape various characters such as the' and the% that I suspected of causing the problem by prefixing a\, however that gives me errors like this one:

Found unknown escape character "\%"

How do I properly escape this query within yaml so that it will run?




The problem was the%, which is a special char in yaml.

So there's two ways to get this to work:

To escape it, it needs to be preceded by another% so:

AND `path` NOT LIKE('checkout/%%')

Alternatively you can avoid using the% as per Jared's comment and instead do:

LEFT(path, 9) != 'checkout/'

People are also looking for solutions to the problem: php - Close support chat after connection timeout with Laravel Echo


Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.