CODEWITHSUNDEEP
typo3typo3-8.x
patryno
patryno

Reputation: 154

query with result based on two tables without join

i want to create query in Query Builder The query should at least looks like (plus default typo3 fields hidden, deleted etc.):


    SELECT DISTINCT
        *
    FROM
        domain_model_topic,
        domain_model_post
    WHERE
        domain_model_topic.uid = domain_model_post.topic
            AND (domain_model_topic.title LIKE '%test%'
            OR domain_model_post.text LIKE '%test%')

Query is usage to search forum for selected word In this query i have one variables from user: its text to search in topics and posts.

Based on documentation from https://docs.typo3.org/typo3cms/CoreApiReference/8.7/ApiOverview/Database/QueryBuilder/

i created something like:


    /** @var \TYPO3\CMS\Core\Database\Query\QueryBuilder $queryBuilder */
    $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('domain_model_topic');
    $queryBuilder->getRestrictions()->removeAll();
    $queryBuilder
        ->select('*')
        ->from('domain_model_post')
        ->from('domain_model_topic')
        ->where(
            $queryBuilder->expr()
                ->eq('domain_model_topic.uid', 'domain_model_post.topic') . ' AND ' .
            $queryBuilder->expr()
                ->like('domain_model_topic.title', $queryBuilder->createNamedParameter('%' . $queryBuilder->escapeLikeWildcards($search->getSWord()) . '%')) . ' OR ' .
            $queryBuilder->expr()
                ->like('domain_model_post.text', $queryBuilder->createNamedParameter('%' . $queryBuilder->escapeLikeWildcards($search->getSWord()) . '%'))
        );
    $test = $queryBuilder->execute()->fetchAll();

but this code cause error Allowed memory size of XXX bytes exhausted for any assigned number of memory

i tried rewrite query to have its simpler (without $queryBuilder->expr(): )


    $queryBuilder
        ->select('*')
        ->from('domain_model_post')
        ->from('domain_model_topic')
        ->where(
    "domain_model_topic.uid = domain_model_post.topic
    AND (domain_model_topic.title LIKE '%" . $variableStringFromUser . "%'
    OR domain_model_post.text LIKE '%" . $variableStringFromUser . "%')"
    );

And it work, but have seriously problems with security, then i tried do add $queryBuilder->createNamedParameter($variableStringFromUser) and that didn't work

My goal is to create search by search word for forum for topics from one table and post from another. I can't use left join because its have seriously performance issue and after some test in mysql, select from two tables give best result (and work in mysql)

What can i do to create query from begining of post in typo3_8 (best with query builder) in secure way (and as clean as possible)

Upvotes: 1

Views: 1420

Answers (1)

patryno
patryno

Reputation: 154

After long trying i found solution to create query which work fast. 


    $queryBuilder
        ->select('*')
        ->from('domain_model_post')
        ->from('domain_model_topic')
        ->orWhere(
            $queryBuilder->expr()->like('domain_model_topic.title', $queryBuilder->createNamedParameter('%' . $queryBuilder->escapeLikeWildcards($search->getSWord()) . '%')),
            $queryBuilder->expr()
                ->like('domain_model_post.text', $queryBuilder->createNamedParameter('%' . $queryBuilder->escapeLikeWildcards($search->getSWord()) . '%'))
        )
        ->andWhere(
            $queryBuilder->expr()->eq('domain_model_topic.uid', 'domain_model_post.topic')
        );

Query for this code:


    SELECT 
        *
    FROM
        `domain_model_post`,
        `domain_model_topic`
    WHERE
        ((`domain_model_topic`.`title` LIKE '%test%')
            OR (`domain_model_post`.`text` LIKE '%test%'))
            AND (`domain_model_topic`.`uid` = domain_model_post.topic)

its work hundred time faster than previous query. I recommend for everyone to try optimize searches by text with doable from and where instead of join

Upvotes: 0

Related Questions