[HELP] MySQL EXISTS() in Cakephp Query Builder

[iRaziul]

I just need to convert this SQL query to Cakephp query builder.

SELECT EXISTS(SELECT 1 FROM `ri_users` WHERE `email` = 'raziul@mail.com')

Thankyou

[odan]

Very easy:

public function existsUserEmail(string $email): bool
{
    $query = $this->queryFactory->newSelect('ri_users')->select('id');
    $query->andWhere(['email' => $email]);

    return !empty($query->execute()->fetch('assoc'));
}

[iRaziul]

The solution you provided is ok. I know that but this is not what I wanted. I wanted to write that raw SQL in cakphp's way.

[odan]

This same result would be using a subselect, but it's much more complicated and not more efficient.

public function existsUserEmail(string $email): bool
{
    $subQuery = $this->queryFactory->newSelect('users');
    $subQuery = $subQuery->select($subQuery->newExpr('1'));
    $subQuery->andWhere(['email' => $email]);

    $query = $this->queryFactory->newQuery();
    $query->select($query->newExpr()->exists($subQuery));

    return !empty($query->execute()->fetch('assoc'));
}

SELECT (EXISTS (SELECT (1) FROM `users` WHERE `email` = :c0))

Alternatively you can construct a statement manually and then fetch rows from it:

https://book.cakephp.org/4/en/orm/database-basics.html#database-basics-binding-values

[iRaziul]

Thanks. That's all I needed