How to write my MySQL query with JOIN logic using JDatabase methods?

Question

I'm having a difficult time translating this SQL script to JDatabase methods because I can't find the AND query method.

SELECT user.firstname, user.lastname, leadership.leadname, leadership.lead_des
FROM CLUB,LEADERSHIP,USER
WHERE CLUB.CLUB_ID=11
AND LEADERSHIP.USER_ID=USER.USER_ID
AND CLUB.CLUB_ID=Leadership.CLUB_ID;

I was looking at the JDatabase documentation but don't see anything in there with AND.

How can I write my query using Joomla's query syntax?

Answer 1

$db = JFactory::getDbo();
$query = $db->getQuery(true)
  ->select('user.firstname, user.lastname, leadership.leadname, leadership.lead_des')
  ->from('CLUB,LEADERSHIP,USER')
  ->where('CLUB.CLUB_ID=11')
  ->where('LEADERSHIP.USER_ID=USER.USER_ID')
  ->where('CLUB.CLUB_ID=Leadership.CLUB_ID');

Multiple where clauses by default clubbed with AND. You can give the glue (AND or OR) explicitly as second option to the where function above however it is optional. Quote from Joomla API docs for JDatabaseQuery->where function:

string $glue - The glue by which to join the conditions. Defaults to AND. Note that the glue is set on first use and cannot be changed.

Or the alternative is you can manually combine in single where clause. You can use this method to mix OR and AND in the where clauses in a complex query.

$query = $db->getQuery(true)
  ->select('user.firstname, user.lastname, leadership.leadname, leadership.lead_des')
  ->from('CLUB,LEADERSHIP,USER')
  ->where('CLUB.CLUB_ID=11 AND LEADERSHIP.USER_ID=USER.USER_ID AND CLUB.CLUB_ID=Leadership.CLUB_ID');

Answer 2

You can use the and clause like so:

$query->where('CLUB.CLUB_ID = USER.USER_ID', 'AND')
      ->where('LEADERSHIP.USER_ID = USER.USER_ID', 'AND')
      ->where('CLUB.CLUB_ID = Leadership.CLUB_ID');

Answer 3

$db = JFactory::getDbo();
$query = $db->getQuery(true);

// Select records from tables based on condition

$query->select('user.firstname, user.lastname, leadership.leadname, leadership.lead_des');
$query->from('CLUB, LEADERSHIP, USER');
$query->where('CLUB.CLUB_ID=11 AND LEADERSHIP.USER_ID=USER.USER_ID AND CLUB.CLUB_ID=Leadership.CLUB_ID');

$db->setQuery($query);
$result = $db->loadObjectList();

// fetch result

foreach($result as $row) {
    echo "First name ".$row->firstname;
    echo "Last name ".$row->lastname;
}

Answer 4

Using commas as a means to JOIN tables is an older style syntax. More recently, MySQL has offered more keywords to better describe the joining intent (improving code readability).

Your commas represent INNER JOINs, and Joomla has a designated query method for them.

Below I will recommend a try {} catch{} block that will aid in the handling and debugging of your code. None of your tables, columns, or values require any backtick or single quote wrapping, so I will not call any $db->qn() or $db->q() methods. If your real query does have variables, or otherwise needs these considerations, my snippet is well-suited to receive these amendments.

$db = JFactory::getDbo();
try {
    $query = $db->getQuery(true)
                ->select(
                    array("a.firstname", "a.lastname", "b.leadname", "b.lead_des")
                  )
                ->from("club a")
                ->innerJoin("leadership b ON a.club_id = b.club_id")
                ->innerJoin("user c ON b.user_id = c.user_id")
                ->where('a.club_id = 11');
    //echo $query->dump();
    $db->setQuery($query);
    $resultset = $db->loadAssocList();
    if (!$resultset) {
        echo "No Rows In Resultset";
    } else {
        foreach ($resultset as $row) {
            echo "<div>";
                echo "<div>First Name: {$row['firstname']}</div>";
                echo "<div>Last Name: {$row['lastname']}</div>";
                echo "<div>Lead Name: {$row['leadname']}</div>";
                echo "<div>Lead Description: {$row['lead_des']}</div>";
            echo "</div>";
        }
    }
} catch (Exception $e) {
    echo "Syntax Error "; // . $e->getMessage();
}