CODEWITHSUNDEEP
mysqlcodeigniter
Ramy Soeg
Ramy Soeg

Reputation: 1

Codeigniter how to do a complex query in active record

How do I do this query in codeigniter without using RAW:

SELECT `accounts`.`id`, `accounts`.`username`, `membs_authority`.`authority`, `membs_info`.`created_at`, `membs_info`.`confirmed`, `membs_hashes`.`sha256`, `membs_hashes`.`scrypt`, `membs_hashes`.`x11` 
FROM `accounts` 
INNER JOIN `membs_authority` 
  ON `membs_authority`.`won` = `accounts`.`secret` 
INNER JOIN `membs_info` 
  ON `membs_info`.`id` = `accounts`.`id` 
INNER JOIN `membs_hashes` 
  ON `membs_hashes`.`id` = `accounts`.`id` 
WHERE
  (`membs_info`.`confirmed` = 1
    AND (
       `membs_authority`.`authority` = 1 OR `membs_authority`.`authority` = 9
    )
  ) 
  AND (
    `membs_hashes`.`sha256` >0 OR `membs_hashes`.`scrypt` >0 OR `membs_hashes`.`x11` >0
  )

the important thing here is the structure of "WHERE"

Upvotes: 0

Views: 71

Answers (1)

Atural
Atural

Reputation: 5439

as Vickel already suggested you can find this information pretty easy in the docs, but on the other hand if you really have no clue, it might help if you see how to do that - so try the following

$query = $this->db
    ->select('a.id, a.username, ma.authority, mi.created_at, mi.confirmed, mh.sha256, mh.scrypt, mh.x11')
    ->from('accounts a')
    ->join('membs_authority ma', 'ma.won = a.secret','inner')
    ->join('membs_info mi', 'mi.id = a.id','inner')
    ->join('membs_hashes` mh', 'mh.id = a.id','inner')
    ->group_start()
        ->where('mi.confirmed', 1)
        ->group_start()
            ->where('ma.authority', 1)
            ->or_where('ma.authority', 9)
        ->group_end()
    ->group_end()
    ->group_start()
        ->where('mh.sha256 >',0)
        ->or_where('mh.scrypt >', 0)
        ->or_where('mh.x11 >', 0)
    ->group_end()
    ->get();

Upvotes: 1

Related Questions