Database join question

[Egocentralis]

After searching a long time why a query in the email plugin did not send any email I did find out I had to create a database join element in the list from where i wanted to send the email. So, my query could retrieve information of the database info what i needed.

Looking into the website i saw that a database join element with persid (names of employees) showed the current user automaticly without a dropdown by putting {my->username} into the "default" textbox of the database join settings page.

I have a database join element in where there are companies listed with address, telephone, email, etc and also a field called "bedrijf_id"

How can I take care that the default value what is printed in the list/form is the bedrijf_id what is belonging to the current user and ofcourse to a company.

Because now it shows a drop down where users have to choose but i need it to show the right value straight away.

Is that possible? And if so, can someone guide me how to achieve this?

That would be really awesome.

 

[juuser]

You do not need to create any databasejoin element for just to send the e-mail if I'm not missing something important in your setup.

You can have in e-mail plugin "send to" query something like:

SELECT a.email FROM #__users AS a
LEFT JOIN bedrijf AS b
ON a.id = b.person_id
WHERE a.id = '{my->id}'

 

[Egocentralis]

Hello ,

Thanks for the reply, I do not totally understand it what you write. I think because we are working with an allready existing website what we are rebuilding for a new project it is difficult to translate this what you say to what is allready written in the to (eval) field.

At this moment the query for sending the email is this:


$db = FabrikWorker::GetDbo();
$q = $db->getQuery(true);
$q
->select('email_adres_bedrijf')
->from('egocentralis_bedrijven')
->where('bedrijf_id IN (SELECT bedrijf_id FROM g4e_orakel_klant_coach WHERE persid = ' . $db->quote('{g4e_orakel_soas___persid_raw}') . ')');
$db->setQuery($q);
$mails = $db->loadObjectList();

foreach ($mails as $mail) {
$mail_array[] = $mail->email;
}

$q_sb = $db->getQuery(true);
$q_sb
->select('email')
->from('g4e_orakel_personeel')
->where('persid IN (SELECT persid FROM g4e_orakel_klant_coach WHERE klantid = ' . $db->quote('{g4e_orakel_soas___klantid_raw}') . ')');
$db->setQuery($q_sb);
$mails_sb = $db->loadObjectList();

foreach ($mails_sb as $mail_sb) {
$mail_array[] = $mail_sb->email;
}

return implode(',', $mail_array);

query $q = not working and not sending a email. Thats why I thougth it is needed to create a database join element. The bedrijf_id is as a field with a number in table egocentralis_bedrijven and also in g4e_orakel_klant_coach. Where the pers_id is the id of the logged in user and also connected to the unique bedrijf_id (company)

query q_sb the second query is working and is sending an email to the one who is submitting this form. Pers_id = unique employer number and klant_id is customer id. g4e_orakel_personeel is where the employee details are stored.

So, how to use your example or how to get the first query $q to send email? I am really stuck at the moment.

 

[Egocentralis]

Ok, i narrowed it a littlebit down and tried to write a query to grab the right email address.


$db = FabrikWorker::GetDbo();
$q = $db->getQuery(true);
$q
->select('email_adres_bedrijf')
->from('egocentralis_bedrijven')
->where('bedrijf_id IN (SELECT bedrijf_id FROM g4e_orakel_personeel WHERE userid = {$my->id})');
$db->setQuery($q);
$mails = $db->loadResult();

if(empty($mails)) {
echo test;
}
else {
return $mails;
}


This query returns the desired email address from the company connected with the user via bedrijf_id. I checked it, and it is returning the right data.

So, in the email i changed the query into:


$db = FabrikWorker::GetDbo();
$q = $db->getQuery(true);
$q
->select('email_adres_bedrijf')
->from('egocentralis_bedrijven')
->where('bedrijf_id IN (SELECT bedrijf_id FROM g4e_orakel_personeel WHERE userid = {$my->id})');
$db->setQuery($q);
$mails = $db->loadObjectList();

foreach ($mails as $mail) {
$mail_array[] = $mail->email;
}


return implode(',', $mail_array);

I skipped the $db->quote() because the sanitizing will happen with the second query..

But, its strange, there is no email sended to the email of the company.. I am almost pulling my hair out!!!! AARGH.. what is it what i do not oversee?

Regards Jap

 

[lcollong]

Try :

$db = FabrikWorker::GetDbo();
$q = $db->getQuery(true);
$q
->select($db->qn('email_adres_bedrijf', 'email'))
->from('egocentralis_bedrijven')
->where('bedrijf_id IN (SELECT bedrijf_id FROM g4e_orakel_personeel WHERE userid = {$my->id})');
$db->setQuery($q);
$mails = $db->loadObjectList();

foreach ($mails as $mail) {
$mail_array[] = $mail->email;
}

return implode(',', $mail_array);

or :

$db = FabrikWorker::GetDbo();
$q = $db->getQuery(true);
$q
->select('email_adres_bedrijf')
->from('egocentralis_bedrijven')
->where('bedrijf_id IN (SELECT bedrijf_id FROM g4e_orakel_personeel WHERE userid = {$my->id})');
$db->setQuery($q);
$mails = $db->loadObjectList();

foreach ($mails as $mail) {
$mail_array[] = $mail->email_adres_bedrijf;
}


return implode(',', $mail_array);

also :
echo $q->dump();
to check the query against phpmyadmin or else

and
echo "<pre>";print_r($mails);echo "</pre>";
after the loadObjectList to see what you get

 

[Egocentralis]

Oh wow , they both work!!!!!!

I try to learn and understand. In the second query you write i see that $mail_array[] = $mail is not the commande to send mail, but its to decide where the email address has to be taken from. ->email_adres_bedrijf; So never thought of that.. This because there was some other query in the email plugin...

like: ->select('email')
->from('g4e_orakel_personeel')
->where('persid IN (SELECT persid FROM g4e_orakel_klant_coach WHERE klantid = ' . $db->quote('{g4e_orakel_soas___klantid_raw}') . ')');
$db->setQuery($q_sb);
$mails_sb = $db->loadObjectList();

foreach ($mails_sb as $mail_sb) {
$mail_array[] = $mail_sb->email;

So i never noticed that the select (email) was called at the end of the query... Thanks for that.

In the first query you do it in a different way. You create something in the line ->select($db->qn('email_adres_bedrijf', 'email')) So the line $mail_array[] = $mail->email; knows where to select the email from.

Is this giving the first line a label? or what did you do exactly?
And what is &db->qn standing for or what is that doing?

Last question, is one of the two examples the best one to use or is that not important because its just another approach??

Many many many many thanks... i feel stupid i missed it, but, i am learning from scratch ...

 

[lcollong]

Sorry for not explaining. But you found it !

$db->qn is an alias of $db->quoteName which is a special function to quote the name of the MySQL tables or column. This function has a second argument allowing to specify a name alias.
This is equivalent to the SQL :

SELECT email_adres_bedrijf AS email FROM ....etc...

The alias allows for renaming the column name in the result sent by MySQL on the SELECT
Thus, the loadObjectList function use "email" instead of the real column name (email_adres_bedrijf )
And the object you run through in the foreach loop "knows" what email is.

This is explained here :https://docs.joomla.org/Selecting_data_using_JDatabase
And the quoteName function : https://api.joomla.org/cms-3/classes/JDatabaseQuery.html

Test and try using debug php options such as print_r, var_dump or $query->dump() is the best to understand ....

 

[Egocentralis]

Cool that is a very usefull explanation...

I think even I understands this..