Geek asks and answers

GROUP BY gives priority to MySQL

I have the following query.

$query_assignments = "SELECT * FROM tb_scheduler_assignments 
                      WHERE company_id = '".$company_id."' OR 
                      dept_id = '".$dept_id."' OR 
                      user_id = '".$user_id."' ORDER BY 
                      due_date GROUP BY purchase_id";

I want this to be one query solution that saves the results for user_idabove dept_idand dept_idabove company_id.

For instance:

  • if the same purchase_idtakes place for the strings obtained through dept_idand user_id, then I only need the result for user_id;
  • if the same purchase_idholds for the strings obtained through company_id and user_id, then I only want the result foruser_id
+3
source share
3 answers

SQL, , SQL-. . PHP .

-, SQL , GROUP BY a, *, .

-, , SQL, , , , ( UNION ALL), , , , . .

+1

" ", , , , , , php. , 3 , , , , , , . , .

$finalArray = array();
foreach ($companyArray as $purchaseID => $companyData) {
    if (empty($deptArray[$purchaseID]) && empty($userArray[$purchaseID])) {
        $finalArray[] = $companyData;
    }
}
foreach ($deptArray as $purchaseID => $deptData) {
    if (empty($userArray[$purchaseID])) {
        $finalArray[] = $deptData;
    }
}
foreach ($userArray as $purchaseID => $userData) {
    $finalArray[] = $userData;
}

, , , . , , .

0
$query_assignments = "SELECT *,
                      IF(user_id = {$user_id}, 30,
                        IF(dept_id = {$dept_id}, 20,
                          IF(company_id = {$company_id}, 10, 0)
                        )
                      ) as priority
                      FROM tb_scheduler_assignments 
                      WHERE company_id = {$company_id} OR 
                      dept_id = {$dept_id} OR 
                      user_id = {$user_id}
                      GROUP BY purchase_id
                      ORDER BY due_date, priority DESC";

You can create a virtual field using the operator if.

user_id:      30 pts
dept_id:      20 pts
company_id:   10 pts
else:          0 pts

WARNING: cannot be indexed!

FIX Syntax: GROUP BYand ORDER BYreordered

0
source

More articles:


All Articles