Forums

Simplify some SQL queries.

Permalink
I've never been great with complex sql queries and I this case I think I need some optimizing. With over 300 users this is starting to get out of hand.
$db= Loader::db();
$atEnabled = $db->GetOne("select akID from AttributeKeys where akHandle=?",'action_enabled');
$actionEnabled = $db->GetAll("select uID, avID from UserAttributeValues where akID=?",$atEnabled);
foreach ($actionEnabled as $saleAr) {
if ($db->GetOne("select value from atBoolean where avID=?",$saleAr['avID'])) {
}
}


Basically I want one query(getAll) where I get all the uID's from UserAttributeValues which have a value of 1 in atBoolean and an avID where akID has the akHandle 'action_enabled'.
I don't know if I'm still making sense haha.
At the moment I using a ton of queries to accomplish this because of that for loop with the if statement.
So I was wondering if someone knew a better and more efficient way to do this.
bleenders
 
INTcommunications replied on at Permalink Best Answer Reply
INTcommunications
I am by all means not a SQL expert, but it seems to me that if you joined these two tables you would only need one sql statement
You are testing for 2 conditions in 2 different tables - Maybe traditional SQL would be easier to read ( not concrete ) in this case. I see that in other applications the more complex the SQL that others go back to traditional sql statements.

i.e. I know that this is not like your sql but it is a sample of joining tables ( Jordandev's crud boilerplate ) 



public function getByBodyTypeId($body_type_id) {
      $sql = "SELECT car.*, manufacturer.name AS manufacturer_name"
           . " FROM {$this->table} car"
           . " LEFT JOIN automobile_manufacturers manufacturer ON manufacturer.id = car.manufacturer_id";
      $vals = array();
      if (!empty($body_type_id)) {
         $sql .= " WHERE car.body_type_id = ?";
         $vals[] = (int)$body_type_id;
      }
      $sql .= " ORDER BY car.{$this->order}";
      return $this->db->GetArray($sql, $vals);
   }



in the WHERE part on yours

WHERE Attribute.akHandler =? AND atBoolen.avID=? etc.
bleenders replied on at Permalink Reply
bleenders
Thanks for you reply mate.
As you pointed out I just needed to join the 2 tables.

Ended up with this 1 query instead of looping tons of times.

$actionEnabled = $db->GetAll("select UserAttributeValues.uID from UserAttributeValues INNER JOIN atBoolean ON UserAttributeValues.avID = atBoolean.avID where UserAttributeValues.akID=? and atBoolean.value=?",array($atEnabled,"1"));