[SOLVED] PDF form detail view template | get label of databasejoin element instead of ID + Solution

[marcq]

Hi,

I'm creating a form detail view PDF template.

I'm trying to retrieve the label of the databasejoin Element "fab_booking_44_repeat.categorie" in a query, I get the id :

1 | 2 | 20.20 |
2 | 10 | 25.00 |
2 | 10 | 42.00 |
3 | 20 | 112.00 |

(First value in the array is the id)

Would appreciate, if someone could explain me how to solve this issue.

Thanks in advance.

Marc

                        $bookingid = JRequest::getVar('rowid');
                        $db = JFactory::getDbo();
                           $query = $db->getQuery(true);
                           $query
                              ->select (array('fab_booking.id', 'fab_booking_44_repeat.categorie', 'fab_booking_44_repeat.nombre_art', 'fab_booking_44_repeat.prix_ttc'))
                              ->from('fab_booking')
                              ->leftJoin('fab_booking_44_repeat ON fab_booking.id = fab_booking_44_repeat.parent_id')
                              ->having('fab_booking.id = ' . $db->quote($bookingid))
                              ->group('fab_booking.id, fab_booking_44_repeat.categorie, fab_booking_44_repeat.nombre_art, fab_booking_44_repeat.prix_ttc');
                           $db->setQuery($query);
                           $row = $db->loadObjectList();
                           echo "<div style='font-family: arial, helvetica, sans-serif; font-size: 11pt;'><UL>";
                           foreach ($row as $item)
                           {
                               echo "<LI> $item->categorie | ";
                               echo "$item->nombre_art | ";
                               echo "$item->prix_ttc | <br />";
                               echo "</LI>";
                           }
                           echo "</UL></div>";

 

[cheesegrits]

You'll have to join the table your join element joins to, and use whatever element on that you use as the label. So an extra join ...

->leftJoin('your_joined_table ON fab_booking_44_repeat.categorie = your_joined_table.id')

... replace your_joined_table with the actual table name, and id with whatever you use as the value in the join (which is usually 'id' but might not be).

.And in the select() use your_joined_table.your_label_element (obviously change the names) instead of fab_booking_44_repeat.categorie.

-- hugh

 

[marcq]

Hi Hugh,

Thanks a lot for you response. I forgot indeed the second left join :

->leftJoin('fab_catering_categories ON fab_booking_44_repeat.categorie = fab_catering_categories.id')

It works now.

I have another issue concerning summing the 2 following values from the select :

fab_booking_44_repeat.nombre_art
fab_booking_44_repeat.prix_ttc

I tried different solutions without success. It is certainly trivial, but I'm stuck. Would appreciate your support.

Marc

 

[marcq]

Hi,

Here the solution :

    $bookingid = JRequest::getVar('rowid');
    $db = JFactory::getDbo();
      $query = $db->getQuery(true);
      $query
         ->select (array('fab_booking.id', 'fab_catering_categories.categories', 'sum(fab_booking_44_repeat.nombre_art) as nombre_art', 'sum(fab_booking_44_repeat.prix_ttc) as prix_ttc'))
         ->from('fab_booking')
         ->leftJoin('fab_booking_44_repeat ON fab_booking.id = fab_booking_44_repeat.parent_id')
         ->leftJoin('fab_catering_categories ON fab_booking_44_repeat.categorie = fab_catering_categories.id')
         ->having('fab_booking.id = ' . $db->quote($bookingid))
         ->group('fab_catering_categories.categories');
      $db->setQuery($query);
      $row = $db->loadObjectList();
      echo "<div style='font-family: arial, helvetica, sans-serif; font-size: 11pt;'>";
      foreach ($row as $item)
      {
          echo "$item->categories | ";
          echo "$item->nombre_art | ";
          echo "$item->prix_ttc | <br />";
          echo "";
      }
      echo "</div>";

Sum :

'sum(fab_booking_44_repeat.nombre_art) as nombre_art'

'sum(fab_booking_44_repeat.prix_ttc) as prix_ttc'

 

[marcq]

Hi,

Strange issue here. Query was returning data during weeks.

I can't say exactly when the query stops returning values, since I haven't test it the last 2 or 3 weeks.

var_dump($row); is returning : array(0) { }

Array is empty, even if data are existing in the "fab_booking_44_repeat", "fab_catering_categories" and "fab_booking" tables. Id's, left join are correct.

I haven't change anything in my bootstrap custom detail template. About 10 other queries are returning their values correctly.

Any idea where the problem could be ?

Cheers, marc

 

[cheesegrits]

Dump the query, and try running it by hand. You may have to cast it to string in the dump, by putting (string) in front of the variable name.

 

[marcq]

Dump the query, and try running it by hand. You may have to cast it to string in the dump, by putting (string) in front of the variable name.

Thank you Hugh,

I found out the issue.

I should have used

->where('fab_booking.id = ' . $db->quote($bookingid))

instead of

->having('fab_booking.id = ' . $db->quote($bookingid))

No clue why it works a few weeks with "having" clause ...

Corrected solution :

    $bookingid = JRequest::getVar('rowid');
    $db = JFactory::getDbo();
      $query = $db->getQuery(true);
      $query
         ->select (array('fab_booking.id', 'fab_catering_categories.categories', 'sum(fab_booking_44_repeat.nombre_art) as nombre_art', 'sum(fab_booking_44_repeat.prix_ttc) as prix_ttc'))
         ->from('fab_booking')
         ->leftJoin('fab_booking_44_repeat ON fab_booking.id = fab_booking_44_repeat.parent_id')
         ->leftJoin('fab_catering_categories ON fab_booking_44_repeat.categorie = fab_catering_categories.id')
         ->where('fab_booking.id = ' . $db->quote($bookingid))
         ->group('fab_catering_categories.categories');
      $db->setQuery($query);
      $row = $db->loadObjectList();
      echo "<div style='font-family: arial, helvetica, sans-serif; font-size: 11pt;'>";
      foreach ($row as $item)
      {
          echo "$item->categories | ";
          echo "$item->nombre_art | ";
          echo "$item->prix_ttc | <br />";
          echo "";
      }
      echo "</div>";