Help with QueryBuilder

đź’» Development
1

Hello!

I’m trying to write the following SQL query with QueryBuilder way:

SELECT * FROM oc_mf_jobs j WHERE j.status="pending" AND NOT EXISTS (
    SELECT 1 FROM oc_mf_jobs WHERE status="processing" AND (
        j.file_source IN (file_source, file_target) OR
        ( j.file_target IS NOT NULL AND j.file_target IN (file_source, file_target) )
    )
) ORDER BY j.priority DESC, j.id LIMIT 1;

This is what i have so far, there should be somehow described inner SQL part with AND NOT EXISTS (…) part:

$qb->select('*')
            ->from($this->getTableName())
            ->where($qb->expr()->eq('status', $qb->createNamedParameter('pending')))
            ->andWhere(.......................)
            ->orderBy('priority', 'DESC')
            ->addOrderBy('id')
            ->setMaxResults(1);

If you know where to start looking – i would really appreciate it.

P.S.: just for details/context — the idea is to select single row, which has “pending” state, while there is no currently any row with “processing” state which has matching file_source or file_target (basically meaning that this file is currently in processing state by other job); the file_target can be null, but not file_source.

Thank you for any help or hints <3

2

Here is the SQL query you provided:

SELECT * 
FROM oc_mf_jobs j 
WHERE j.status = 'pending' 
  AND NOT EXISTS (
    SELECT 1 
    FROM oc_mf_jobs 
    WHERE status = 'processing' 
      AND (
        j.file_source IN (file_source, file_target) OR
        (j.file_target IS NOT NULL AND j.file_target IN (file_source, file_target))
      )
  ) 
ORDER BY j.priority DESC, j.id 
LIMIT 1;

Here’s how to translate this SQL query into QueryBuilder PHP syntax:

$subQuery = $qb->createQueryBuilder();
$subQuery->select('1')
    ->from($this->getTableName())
    ->where($subQuery->expr()->eq('status', $subQuery->createNamedParameter('processing')))
    ->andWhere(
        $subQuery->expr()->orX(
            $subQuery->expr()->in('j.file_source', ':file_source_target'),
            $subQuery->expr()->andX(
                $subQuery->expr()->isNotNull('j.file_target'),
                $subQuery->expr()->in('j.file_target', ':file_source_target')
            )
        )
    );

$qb->select('*')
    ->from($this->getTableName(), 'j')
    ->where($qb->expr()->eq('j.status', $qb->createNamedParameter('pending')))
    ->andWhere($qb->expr()->notExists($subQuery->getSQL()))
    ->orderBy('j.priority', 'DESC')
    ->addOrderBy('j.id')
    ->setMaxResults(1)
    ->setParameter(':file_source_target', ['file_source', 'file_target']);

$sql = $qb->getSQL();

Explanation:

  1. Subquery Setup:
    • The subquery checks if there are any jobs with the processing status that have the same file_source or file_target as the main query job.
  2. Logical Operators:
    • orX combines conditions with an OR logical operator.
    • andX combines conditions with an AND logical operator.
  3. Main Query Setup:
    • The main query selects the job with the pending status.
    • The notExists method ensures no job with the processing status meets the specified conditions.
  4. Parameter Binding:
    • The setParameter method binds parameters for the IN clause, ensuring the SQL query is correctly generated.

(No guarantee)

I hope this could inspire you!

Much and good luck,
ernolf

1 Like
3

@ernolf Thank you for the help.

I would love to test it, but notExists is not defined, basically missing from ExpressionBuilder. Any idea how to do it still?

4

… maybe by using LEFT JOIN and NULL checking like this:

$qb = $this->getConnection()->createQueryBuilder();

// Create the subquery to check for conflicting processing jobs
$subQuery = $qb->createQueryBuilder();
$subQuery->select('1')
    ->from($this->getTableName(), 'sq')
    ->where('sq.status = :processing')
    ->andWhere(
        $subQuery->expr()->orX(
            'sq.file_source = j.file_source',
            'sq.file_target = j.file_source',
            $subQuery->expr()->andX(
                'j.file_target IS NOT NULL',
                'sq.file_source = j.file_target',
                'sq.file_target = j.file_target'
            )
        )
    );

// Main query
$qb->select('j.*')
    ->from($this->getTableName(), 'j')
    ->leftJoin('j', '(' . $subQuery->getSQL() . ')', 'sq', '1 = 1')
    ->where('j.status = :pending')
    ->andWhere('sq.file_source IS NULL')  // Ensures no matching processing jobs
    ->orderBy('j.priority', 'DESC')
    ->addOrderBy('j.id')
    ->setMaxResults(1)
    ->setParameter(':processing', 'processing')
    ->setParameter(':pending', 'pending');

$sql = $qb->getSQL();
  1. Subquery:
    • The subquery selects 1 from the same table (oc_mf_jobs) where the status is processing.
    • It checks for matching file_source or file_target values against the main query’s job.
  2. Left Join:
    • The main query uses a LEFT JOIN to join the subquery. If no matching rows are found, the sq.file_source will be NULL.
  3. Where Conditions:
    • The where clause of the main query ensures that the job status is pending.
    • The andWhere('sq.file_source IS NULL') ensures that there are no conflicting processing jobs.
  4. Parameter Binding:
    • Parameters :processing and :pending are bound for use in the query.

This approach avoids the need for notExists by using a LEFT JOIN with a NULL check, which is a common SQL pattern for achieving the same result.

again, absolutely no guarantee

ernolf

5

Thank you for the help, but this still doesn’t work.

Execution gives error:

Code: 0
Message: Named parameter “pending” does not have a bound value.

This is my code. Its inside a method, which is expected to return an entity, which can be absent (there might not be next job, a valid situation). The method is a part of a mapper class which extends QBMapper, I’m telling this details, because in your code you use $this->getConnection(), i don’t have it. I do have db property which is injected via contructor and any query I build, I do $this->qb->getQueryBuilder(). Maybe this is important, so i mentioning this.

So the method which contains code you gave is following (and it gives error i mention above).

    public function findNext(): ?Job {
        $subQuery = $this->db->getQueryBuilder();
        $subQuery->select('1')
            ->from($this->getTableName(), 'sq')
            ->where('sq.status = :processing')
            ->andWhere(
                $subQuery->expr()->orX(
                    'sq.file_source = j.file_source',
                    'sq.file_target = j.file_source',
                    $subQuery->expr()->andX(
                        'j.file_target IS NOT NULL',
                        'sq.file_source = j.file_target',
                        'sq.file_target = j.file_target'
                    )
                )
            );

        $qb = $this->db->getQueryBuilder();
        $qb->select('j.*')
            ->from($this->getTableName(), 'j')
            ->leftJoin('j', '(' . $subQuery->getSQL() . ')', 'sq', '1 = 1')
            ->where('j.status = :pending')
            ->andWhere('sq.file_source IS NULL')
            ->orderBy('j.priority', 'DESC')
            ->addOrderBy('j.id')
            ->setMaxResults(1)
            ->setParameter(':processing', 'processing')
            ->setParameter(':pending', 'pending');

        $entities = $this->findEntities($qb);
        return count($entities) == 1 ? $entities[0] : null;
    }

I don’t see why “pending” doesn’t have bound value as it clearly does. Maybe you see.

Also, do you know a way to simply execute a query from SQL? I checked implementations of IQueryBuilder and its just QueryBuilder class. And findEntities which is called at the end expects only IQueryBuilder.

Thank you for any hints.

6

try

    ->setParameter('processing', 'processing')
    ->setParameter('pending', 'pending');

without colon.

To execute a SQL query directly, you can use the methods provided by the Doctrine DBAL library, like

// 1. Create the SQL query
$sql = "
    SELECT * 
    FROM oc_mf_jobs j 
    WHERE j.status = :pending 
    AND NOT EXISTS (
        SELECT 1 
        FROM oc_mf_jobs sq 
        WHERE sq.status = :processing 
        AND (
            j.file_source IN (sq.file_source, sq.file_target) OR
            (j.file_target IS NOT NULL AND j.file_target IN (sq.file_source, sq.file_target))
        )
    ) 
    ORDER BY j.priority DESC, j.id 
    LIMIT 1
";

// 2. Set the parameters
$params = [
    'pending' => 'pending',
    'processing' => 'processing'
];

// 3. Execute the query
$stmt = $this->db->getConnection()->executeQuery($sql, $params);

// 4. Fetch the results
$result = $stmt->fetchAll();

// Process or return the results
return count($result) == 1 ? $result[0] : null;

HTH,

ernolf

1 Like