Calc count for multichoice dropdown

[jh]

Hi

Ive been trying to do a calc count based on a values present in a multichoice drop down element. I have tried the following code but I assume because the element 'associated_fac' (the dropdown element) has data stored along the lines of 2,4,7 (or however) it does not equal the id number:

$db =& JFactory::getDBO();
$query1 = "SELECT COUNT(*) FROM 'designer_table' WHERE 'associated_fac' = '{factory_table___id}';
$db->setQuery($query1);
$query1 = $db->loadResult();

Is there a way (perhaps 'contains' or exactly equal to) that makes this code work?

Thank you in advance for any help.

Kind Regards

 

[lousyfool]

$fact_id = '{factory_table___id}';
$mydb = JFactory::getDbo();
$query = $mydb->getQuery(true);
$query->select('id')
    ->from($mydb->quoteName('designer_table'))
    ->where($mydb->quoteName('associated_fac') . ' = ' . $mydb->quote($fact_id));
$mydb->setQuery($query);
$mydb->execute();
$result = $mydb->getNumRows();

https://docs.joomla.org/Selecting_data_using_JDatabase#getNumRows.28.29

 

[jh]

Hi

Thank you very much for your message and code. Ive tried it out but it is not returning any value. I think it is because of the way the values of 'associated_fac' are stored in the database so the field does not contain a single value. I could be wrong but this is my guess, so nothing matches as a single value (so nothing is returned).

Any suggestions on how to make this work would be great.

Kind Regards

 

[lousyfool]

Sorry, in my previous post I was too much focused on getting your query right in "joomla format".

Right, multi-select elements like checkboxes or multi-dropdowns (not DBjoins, though) are storing values in these formats:
- Multiple options selected: JSON array, e.g. ["123","456"]
- Single option selected: value only, e.g. 123

So, you need the extra steps of converting the field to a comma-separated list of values, and using "IN" in the query. Examples:
http://fabrikar.com/forums/index.php?threads/help-for-json_decode.49141/
http://fabrikar.com/forums/index.php?threads/retrieve-data-from-picklist.49498/#post-258901

So, try this:

$fact_id = FabrikWorker::JSONtoData('{factory_table___id}', true);
$fact = implode(', ', $fact_id);
$mydb = JFactory::getDbo();
$query = $mydb->getQuery(true);
$query->select('id')
    ->from($mydb->quoteName('designer_table'))
    ->where($mydb->quoteName('associated_fac') . ' IN (' . $mydb->quote($fact) . ')');
$mydb->setQuery($query);
$mydb->execute();
$result = $mydb->getNumRows();

 

[jh]

Hi

Thanks for getting back to me and explaining about the 'array' values, has made a lot more sense to me now and very useful to know.

Unforunately I havent been able to get the code you kindly offered to work but I am wondering if that is because of what I am trying to do rather than the code itself. The page was returning a 500 error.

I now have a clac element running in 'designer table' which returns the values as expected:

$fact_id = FabrikWorker::JSONtoData('{associated_factories_test_id}', true);
$fact = implode(', ', $fact_id);
return $fact;

What I want is to be able to get a count of all the times the individual values appear (eg 1 = 3, 2 = 4, 3 = 2) etc. The values are the Primary Key in another table so I was trying to use the placeholder {factory_id} to get the count to work, running the calc in the 'factory table'.

Thank you for any assistance you can provide or any further pointers.

Kind Regards

 

[lousyfool]

It's getting confusing... so let's try to clarify:

(A) You have a list "designer_table" including a multi-select dropdown (not a DBjoin) named {designer_table___associated_fac} storing values which happen to be the same as the PKs in another list "factory_table", namely {factory_table___id}.
Correct?

(B) For each {factory_table___id}, you want to display, how many records in "designer_table" are containing it in the field {designer_table___associated_fac}.
Correct?

(C) Initially, you attempted to do so in a calc field in "designer_table" but, for obvious reasons, you figured since that this is not practical.
Correct?

(D) So, now it's about the -- clearly better -- idea of using a calc field in "factory_table" for the purpose.
Correct?

(E) There's no connection in between the two lists, like a list join or DBjoin or so; instead you've set up {designer_table___associated_fac} with values to be maintained manually.
Correct?

If all of the above is correct, indeed, why not make {designer_table___associated_fac} a multi-select "databasejoin" element? Your use case is exactly what it's there for.
If you don't have many records in "designer_table" yet, it's simple and would make a lot of things easier, and if only taking out potential human error when adding factories. It would (auto-)create a new "repeat" table holding PKs of both tables, which are simple to work with -- including counting one or the other.

Perhaps add a new DBjoin element to "designer_table" to see how it's working.
http://fabrikar.com/forums/index.php?wiki/database-join-element/

 

[troester]

Multiselect dbjoins are not stored as json in the database but in an additional 'many-to-many' table your-table_repeat_ your-element.
So you must select from this repeat table ... Where parent_ id= ...

Gesendet von meinem SM-G930F mit Tapatalk

 

[jh]

Hi

Thank you both for your replies.
Lousyfool, thank you for helping with this and sorry for the confusion:
A - 'associated_fac' IS a dbjoin, showing as a multichoice dropdown in the form (factory table values). Apologies I did not make this clear.
B = correct that is what I wanted.
C = correct
D = correct
E = sorry for not making it clear the connection was a dbjoin.The values of 'associated_fac' are joined from the factory table.

So I just wanted a count to show in the factory table of all the times the PK appears in 'associated_fac'. I have been able to do the counts of single dbjoin values succesfully but not when the returned values are from a multi dbjoin.

So as a calc element in the factory table I had tried:

$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery->select('COUNT(*) AS mycount')->from('designer_table')->where('associated_fac = "{factory_table___id}"');
$myDb->setQuery($myQuery);
return $myDb->loadResult();

but as 'associated_fac' doesnt return a single value nothing is returned.

From what Troester has kindly replied I think it should now look like:

$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery->select('COUNT(*) AS mycount')->from('designer_table_repeat')->where('associated_fac = "{factory_table___id}"');
$myDb->setQuery($myQuery);
return $myDb->loadResult();

However this is returning a 500 error.

Thank you both for your help and apologies that my lack of experience has caused confusion.

Kind Regards

 

[lousyfool]

Ok, with this finally clear, you need to look at what you want to count where, plus one way or another get your syntax right.

In phpMyAdmin you can find the "...repeat..." table as created by the DBjoin field. I'm assuming this table is named "designer_table_repeat_associated_fac" and the relevant column inside is "associated_fac".
Please double-check.

Then the query in your calc element in "factory_table" should be:

$fac = '{rowid}';
$mydb = JFactory::getDbo();
$query = $mydb->getQuery(true);
$query->select('associated_fac')
    ->from($mydb->quoteName('designer_table_repeat_associated_fac'))
    ->where($mydb->quoteName('associated_fac') . ' = ' . $mydb->quote($fac));
$mydb->setQuery($query);
$mydb->execute();
$somany = $mydb->getNumRows();
return 'used ' . $somany . ' times';

If names are different in phpMyAdmin, please adjust them accordingly but don't change the syntax.

 

[jh]

Thank you sincerely for your assistance and time spent on this, this worked perfectly. Really pleased to have this on my site.