Проблема с QueryBuilder symfony (знак '?')
Проблема с QueryBuilder symfony (знак '?')
LIKE '%?amp=1%' мне нужно это выполнить в запросе. Но QueryBuilder воспринимает "?" как символ подстановки. И подставляет параметр.
$qb
->select($this->stringQueryProvider->fetchDataSummaryAnnotationPage($annotationFilter, $pageSelect))
->setParameter('type', $this->getType())
->setParameter('projectUrl', $project->getGscaUrl())
->setParameter('startLast', $annotationFilter->getStartDate())
->setParameter('endLast', $annotationFilter->getEndDate())
->setParameter('startPrev', $annotationFilter->getStartDayPreviousPeriod())
->setParameter('endPrev', $annotationFilter->getEndDayPreviousPeriod());
То есть при попытке выполнить запрос я вижу вот это: page_url LIKE '%'https://habi.co/'amp=1%' ,где вместо '?' вставляется параметр projectUrl. Как можно объяснить QueryBuilderу что этот вопрос нужно считать как обыкновенный символ?
P.S. обратный слеш не помог. addcslashes($conditionValue, '\\%_?') тоже не помог.
сам запрос:
SELECT if(previousClicks == 0 and lastClicks != 0, 100, if(previousClicks == 0 and lastClicks == 0, 0, (diffClicks/previousClicks) * 100)) as percentClicks, sum(ifNull(lastClicks, 0)) as lastClicks, sum(ifNull(previousClicks, 0)) as previousClicks, (lastClicks-previousClicks) as diffClicks, if(previousImpressions == 0 and lastImpressions != 0, 100, if(previousImpressions == 0 and lastImpressions == 0, 0, (diffImpressions/previousImpressions) * 100)) as percentImpressions, sum(ifNull(lastImpressions, 0)) as lastImpressions, sum(ifNull(previousImpressions, 0)) as previousImpressions, (lastImpressions-previousImpressions) as diffImpressions, if(previousCtr == 0 and lastCtr != 0, 100, if(previousCtr == 0 and lastCtr == 0, 0, (diffCtr/previousCtr) * 100)) as percentCtr, if(lastImpressions == 0, 0, lastClicks/lastImpressions) as lastCtr, if(previousImpressions == 0, 0, previousClicks/previousImpressions) as previousCtr, (lastCtr-previousCtr) as diffCtr, if(previousPosition == 0 and lastPosition != 0, 100, if(previousPosition == 0 and lastPosition == 0, 0, (diffPosition/previousPosition) * 100)) as percentPosition, avg(ifNull(lastPosition, 0)) as lastPosition, avg(ifNull(previousPosition, 0)) as previousPosition, if(previousPosition == 0 and lastPosition != 0, lastPosition, if(previousPosition != 0 and lastPosition == 0, -(previousPosition), (previousPosition-lastPosition))) as diffPosition FROM ( SELECT last.page_url, sum(last.clicks) as lastClicks, sum(last.impressions) as lastImpressions, lastClicks/lastImpressions as lastCtr, avg(ifNull(last.position, 0)) as lastPosition from gsca_search_console_v2 as last where ( (last.project_url = :projectUrl) and (last.date >= :startLast) and (last.type = :type) and (last.date <= :endLast) and (sign > 0) and (page_url in (SELECT page_url FROM gsca_search_console_v2 WHERE (project_url = :projectUrl) AND (date >= :startPrev) AND (date <= :endLast) AND (type = :type) AND ( page_url LIKE '%/venta-apartamentos%' AND page_url LIKE '%?amp=1%' ) )) ) group by page_url ) lastStats full outer join ( SELECT previous.page_url, sum(previous.clicks) as previousClicks, sum(previous.impressions) as previousImpressions, previousClicks/previousImpressions as previousCtr, avg(ifNull(previous.position, 0)) as previousPosition from gsca_search_console_v2 as previous where ( (previous.project_url = :projectUrl) and (previous.date >= :startPrev) and (previous.date <= :endPrev) and (previous.type = :type) and (sign > 0) and (page_url in (SELECT page_url FROM gsca_search_console_v2 WHERE (project_url = :projectUrl) AND (date >= :startPrev) AND (date <= :endLast) AND (type = :type) AND ( page_url LIKE '%/venta-apartamentos%' AND page_url LIKE '%?amp=1%' ) )) ) group by page_url ) previousStats USING page_url
Попробовал переделать через параметры:
SELECT if(previousClicks == 0 and lastClicks != 0, 100, if(previousClicks == 0 and lastClicks == 0, 0, (diffClicks/previousClicks) * 100)) as percentClicks, sum(ifNull(lastClicks, 0)) as lastClicks, sum(ifNull(previousClicks, 0)) as previousClicks, (lastClicks-previousClicks) as diffClicks, if(previousImpressions == 0 and lastImpressions != 0, 100, if(previousImpressions == 0 and lastImpressions == 0, 0, (diffImpressions/previousImpressions) * 100)) as percentImpressions, sum(ifNull(lastImpressions, 0)) as lastImpressions, sum(ifNull(previousImpressions, 0)) as previousImpressions, (lastImpressions-previousImpressions) as diffImpressions, if(previousCtr == 0 and lastCtr != 0, 100, if(previousCtr == 0 and lastCtr == 0, 0, (diffCtr/previousCtr) * 100)) as percentCtr, if(lastImpressions == 0, 0, lastClicks/lastImpressions) as lastCtr, if(previousImpressions == 0, 0, previousClicks/previousImpressions) as previousCtr, (lastCtr-previousCtr) as diffCtr, if(previousPosition == 0 and lastPosition != 0, 100, if(previousPosition == 0 and lastPosition == 0, 0, (diffPosition/previousPosition) * 100)) as percentPosition, avg(ifNull(lastPosition, 0)) as lastPosition, avg(ifNull(previousPosition, 0)) as previousPosition, if(previousPosition == 0 and lastPosition != 0, lastPosition, if(previousPosition != 0 and lastPosition == 0, -(previousPosition), (previousPosition-lastPosition))) as diffPosition FROM ( SELECT last.page_url, sum(last.clicks) as lastClicks, sum(last.impressions) as lastImpressions, lastClicks/lastImpressions as lastCtr, avg(ifNull(last.position, 0)) as lastPosition from gsca_search_console_v2 as last where ( (last.project_url = :projectUrl) and (last.date >= :startLast) and (last.type = :type) and (last.date <= :endLast) and (sign > 0) and (page_url in (SELECT DISTINCT page_url FROM gsca_search_console_v2 WHERE (project_url = :projectUrl) AND (date >= :startPrev) AND (date <= :endLast) AND (type = :type) AND ( page_url LIKE :conditionValueEscaped0 AND page_url LIKE :conditionValueEscaped1 ) )) ) group by page_url ) lastStats full outer join ( SELECT previous.page_url, sum(previous.clicks) as previousClicks, sum(previous.impressions) as previousImpressions, previousClicks/previousImpressions as previousCtr, avg(ifNull(previous.position, 0)) as previousPosition from gsca_search_console_v2 as previous where ( (previous.project_url = :projectUrl) and (previous.date >= :startPrev) and (previous.date <= :endPrev) and (previous.type = :type) and (sign > 0) and (page_url in (SELECT DISTINCT page_url FROM gsca_search_console_v2 WHERE (project_url = :projectUrl) AND (date >= :startPrev) AND (date <= :endLast) AND (type = :type) AND ( page_url LIKE :conditionValueEscaped0 AND page_url LIKE :conditionValueEscaped1 ) )) ) group by page_url ) previousStats USING page_url
Параметры:
array:8 [
"type" => "page"
"projectUrl" => "https://habi.co/"
"startLast" => "2022-05-25"
"endLast" => "2022-05-31"
"startPrev" => "2022-05-18"
"endPrev" => "2022-05-24"
"conditionValueEscaped0" => "%/venta-apartamentos%"
"conditionValueEscaped1" => "%?amp=1%"
]
Ошибка осталась...
Ответы (1 шт):
Автор решения: Borislav
→ Ссылка
Спокойно работает в Doctrine 2.6
$qb = $this->createQueryBuilder('ags');
$qb->andWhere(
$qb->expr()->like('ags.loginApi', ':likeExpr')
);
$qb->setParameter('likeExpr', '?helloWorld');
Генерируемый SQL:
string(84) "SELECT ags FROM App\Entity\Test\TestAGS ags WHERE ags.loginApi LIKE :likeExpr"
Все параметры будут подставлены на моменте подготовки запроса и обработаны должным образом, после же, знак ? не будет иметь никакого значения и интерпретируется как строка.