Search code examples
phpmysqlidiorm

How to write this on Idiorm syntax


in MySQL syntax

SELECT users.*, roles.name, departments.name FROM users
INNER JOIN roles ON users.roleId = roles.id
INNER JOIN departments ON users.department = departments.id
WHERE users.roleId = 3 AND (
     users.name LIKE '%%' OR
     users.email LIKE '%%' OR
     users.phone LIKE '%%' OR
     departments.name LIKE '%%' OR
     roles.name LIKE '%%'
)
ORDER BY update-date DESC
LIMIT 0, 10

I'm trying to write this in Idiorm syntax, but WHERE blabla AND (LIKE OR LIKE) does not work

 $backRows = ORM::for_table('users')
        ->select(['departments.name', 'roles.name', 'users.*'])
        ->join('roles', array(
            'roles.id', '=', 'users.roleId'
        ))
        ->join('departments', array(
            'departments.id', '=', 'users.department'
        ))
        ->where('users.roleId', 3)
        // don't work ->where_raw('(`users.name` = ? OR `users.email` = ?)', ['%'.$search.'%', '%'.$search.'%'])
        ->limit($body['perPage'])->offset($body['currentPage'] * $body['perPage'] - $body['perPage'])
        ->{ 'order_by_' . $body['order'] }($body['sort'])
        ->findArray();

Solution

  • после 10и часов курения доков заработало вот так:

    $backRows = ORM::for_table('users')
        ->select(['departments.name', 'roles.name', 'users.*'])
        ->join('roles', ['roles.id', '=', 'users.roleId'])
        ->join('departments', ['departments.id', '=', 'users.department'])
        ->where_not_equal('users.roleId', 3)
        ->where_raw('(users.name LIKE ? OR users.email LIKE ? OR users.phone LIKE ? OR roles.name LIKE ? OR departments.name LIKE ?)', ['%'.$search.'%', '%'.$search.'%', '%'.$search.'%', '%'.$search.'%', '%'.$search.'%'])
        ->limit($body['perPage'])->offset($body['currentPage'] * $body['perPage'] - $body['perPage'])
        ->{ 'order_by_' . $body['order'] }($body['sort'])
        ->findArray();