Calc element use value from another list

[joki94]

Hello again,

I have one table called user_data where I want to collect some user data, I have in this table the elements:

name = field
userpoints = calc
email = field

Then I have another table called user_points where I want to have the possibility to add user points. So I added these elements

user = database join from table user_data
from = date
to = date
points = field

Now I try to get the calc element working.

I want that the value of the calc element userpoints will be generated by the field points, corresponding to the username. Means

I have a user Test1 and Test2 created in the table user_data.
Now I added a from and to date and 200 points to the user Test1 in the table user_points
I also added a from and a to date and 500 points to the user Test2 in the table user_points

So after duing this the element userpoints in the table user_data for Test1 should have as value 200 and for Test2 the value 500

Hope it is clear what I mean and hope someone can help.

Kind Regards and thanks,

joki94

 

[Sophist]

Yes - I can help. For the calc in user_data use something like:

// Sum the points for this user

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

$query->clear()
  ->select('SUM(points)')
  ->from($db->quoteName('points'))
  ->where($db->quoteName('points.username') . ' = ' . $db->quote('{user_data___name}'));

$db->setQuery($query);
$count = $db->loadResult();

return $count;

This will by default be calculated when you load the user_data form, when you save the form and when you display the list. When your list gets big, running a query like this on every row individually may get slow, in which case you need to set the calc so it does not get done when you show the list, and instead provide an update query in a PHP Event List plugin - in the onPreLoadData event like:

// Update points before displaying list

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

$subQuery->clear()
  ->select(array($db->quoteName('username'),'COUNT(*) AS count'))
  ->from($db->quoteName('points'));

$value = 'IFNULL(' . $db->quoteName('points.count') . ', 0)';

$query->clear()
  ->update($db->quoteName('user_data'))
  ->leftjoin('(' . $subQuery . ') . ' ON ' . $db->quoteName('points.username') . ' = ' . $db->quoteName('user_data.user'))
  ->set($db->quoteName('user_data.userpoints') . ' = ' . $value)
  ->where($db->quoteName('userpoints') . ' <> ' . $value);

$db->setQuery($query);
try
{
  $db->execute();
}
catch (Exception $e)
{
  JFactory::getApplication()->enqueueMessage('Update of estate fealties failed: ' . $e->getMessage(), 'error');
}

 

[joki94]

Thank you Sophist,

I tried your code, but I got the error that Table 'web40_db9.points' doesn't exist

The names are correct, can you take again a look and maybe sayme what I am doing wrong?

Kind Regards,

joki94

 

[Sophist]

Well - you never said what your second table was called. So I assumed it was points but is could be user_points or points_for_user or awards or user_awards or ...

As I said, you need "something like" this code.

 

[joki94]

Sorry I am not familar with MySql

My first table called user_data and my second called user_points. I changed points to user_points but than I got the error, Unknown column 'user_points' in 'field list'

Sorry again, I added the missing information below.

Kind Regards,

joki94

 

[Sophist]

That is because you changed both the table "points" and the column "points" to user_points. Try:

// Sum the points for this user

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

$query->clear()
->select('SUM(points)')
->from($db->quoteName('user_points'))
->where($db->quoteName('user_points.username').' = '.$db->quote('{user_data___name}'));

$db->setQuery($query);
$count=$db->loadResult();

return$count;

 

[joki94]

Thanks again, I found it out that I changed to much. You was to quick, so I couldnt say you that I found the issue. Thanks anyway.

But unfortunately it displays nothing now. No value

What I did wrong?

Kind Regards,

joki94

 

[joki94]

I will try to solve it on my own a liitle bit. Maybe I can learn how it works. Thanks for you help.

Kind Regards, joki94

 

[joki94]

it works, thank you so much. I only had to use the id in user_data

So the where clause is
->where($db->quoteName('user_points.user').' = '.$db->quote('{user_data___id}'));

because I used the id as the value for the databasejoin.

Thank you so much

Kind regards, joki94

 

[Sophist]

np.

 

[joki94]

Hi Sophist,

I need your help again.

I added the php list event like you said but I got an error message:
Update of estate fealties failed: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT `username`,COUNT(*) AS count FROM `points` ON `points`.`username` = `user' at line 3

I am not so good with mysql but I tried to find a solution. Finaly I changed the innerjoin a bit but without success.

This is my actual code, can you help me to get it working?

// Update points before displaying list

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

$subQuery->clear()
  ->select(array($db->quoteName('username'),'COUNT(*) AS count'))
  ->from($db->quoteName('points'));

$value = 'IFNULL(' . $db->quoteName('points.count') . ', 0)';

$query->clear()
  ->update($db->quoteName('user_data'))
  ->join('LEFT',( $subQuery) . ' ON ' . $db->quoteName('points.username') . ' = ' . $db->quoteName('user_data.user'))
  ->set($db->quoteName('user_data.userpoints') . ' = ' . $value)
  ->where($db->quoteName('userpoints') . ' <> ' . $value);

$db->setQuery($query);
try
{
  $db->execute();
}
catch (Exception $e)
{
  JFactory::getApplication()->enqueueMessage('Update of estate fealties failed: ' . $e->getMessage(), 'error');
}

Thank you and kind regards, joki94

 

[Sophist]

No - sorry - I don't have time to write your code for you. If you want help like that please take out a Fabrikar subscription and Hugh will help you.

 

[joki94]

Hi Sophist,

I dont want that you write my code and I am very happy for all your help but I already tried to solve it on my own with reading a lot of posts. Finally I solved it by deleting the plugin and update database directly in calc field.

Thank you again for your help.

Kind Regards, joki94

 

[Sophist]

The calc field does update the database. But because it is dependent on the time that you view it, you need to update it whenever you view.

Providing you set Calc only on save to No, it will work when you display the list - but if your list has many rows, or if you try to display many rows at once, then when you display the list it may take a very long time or even fail because it is doing a database update for every row individually.