2
votes
How to retrieve RSForm submission data grouped by SubmissionId?
You actually don't need to perform multiple joins with the same table to gather your desired results. What is best suited is a "pivot" technique. There is no cleaner way to deal with rsform's ...
- 4,781
2
votes
Accepted
PlotALOT RSForm Query showing only one result
From this sample data:
INSERT INTO `o2pe0_rsform_submission_values` (`SubmissionValueId`, `FormId`, `SubmissionId`, `FieldName`, `FieldValue`) VALUES
(2124732, 23, 47529, 'SHELTER', 'Bellville'),
(...
- 4,781
2
votes
Accepted
How to refactor two select queries on RSForm submission data?
Here is what I believe the mySQL query to be with (1,2,3) substituted in for your imploding of results because I wanted to check the mySql syntax of it. I don't have time to convert into PHP right ...
- 1,901
1
vote
How to re-write sql for joomla JDatabase
If you are new, then I'd like to include some basic error checking as well as printing out the generated query and the 2-column resultset.
Untested Code:
$db = JFactory::getDBO();
try {
$query =...
- 4,781
1
vote
How to re-write sql for joomla JDatabase
I think you are asking, Joomla code to execute query ?
if yes
$db = JFactory::getDBo();
$query = $db->getQuery(true);
$query->select("MAX(CASE WHEN v.FieldName=".$db->q('Team_Name')." THEN ...
- 111
1
vote
Accepted
Joomla mysql query - group_contact
I really can't see the use in retrieving the SubmissionValueIds or FormIds associated with the rows of data after you pivot into an associative structure. When you fold the original rows together, ...
- 4,781
1
vote
How to join custom fields data to users table without extra rows in result set
To better understand your scenario, I've created this db-fiddle with your sample data and made a basic SQL query to display the JOINed data as a tabular result set.
SELECT u.id, u.name, u.email, ugm....
- 4,781
1
vote
Accepted
PieChart For RSForm for PlotaLot based on a dropdown field
Add a HAVING clause that pre-qualifies aggregrate data in your derived table. The following condition will require that a submission contains a Part No value of 5.
HAVING COUNT(FieldName = 'Part No' ...
- 4,781
1
vote
Show article fields in module
Assuming this is for Joomla 3, you can use the following:
use Joomla\CMS\Factory;
use Joomla\CMS\MVC\Model\BaseDatabaseModel;
\JLoader::register('FieldsHelper', JPATH_ADMINISTRATOR . '/components/...
- 26.8k
1
vote
Accepted
Join #__content with #__fields_values then filter and sort using #__fields_values data
I have reverse engineered a minimal verifiable example on db-fiddle.com based on your question and answer details.
Using my reduced and generic sample data with the sql in your answer, the output is: (...
- 4,781
1
vote
Accepted
RSFORM combine 2 submission form results in one Plotalot view
I think the simplest way forward is to use a JOIN to relate your first query to your second query. An INNER JOIN (same as JOIN) will only generate rows in the result set where both "derived"...
- 4,781
1
vote
Accepted
Charts/Graphs for RSFORM using mysql for VChart and/or PlotaLot
This will rely heavily on the magic of a "pivot" technique. Here are some more examples in JSE to compare/contrast with https://joomla.stackexchange.com/questions/tagged/pivot.
Basically, a ...
- 4,781
1
vote
How to get Custom Fields related to Joomla Content?
Let's say you have two custom fields in your database. In the #__fields_values table:
1 relates to the custom field called "genre"
2 relates to the custom field called "somethingelse"
To use pure ...
- 4,781
1
vote
How to refactor two select queries on RSForm submission data?
Yes, your queries can certainly use refactoring. Using two separate calls on the same table is asking MySQL to do needless extra work.
To convert your queries into plain English, you want to:
...
- 4,781
1
vote
How to retrieve RSForm submission data grouped by SubmissionId?
Here you are:
$query->select($db->qn('s.SubmissionId','id'))
->select($db->qn('s.DateSubmitted','date'))
->select($db->qn('sv.FieldValue','status'))
->...
- 1,130
1
vote
Joomla 3.3.6 Union SQL Issue
A few points:
You have really made a rod for your back by not "normalizing" your org table. My strong recommendation is to restructure your org table to have a type column versus three static ...
- 4,781
1
vote
Joomla 3.3.6 Union SQL Issue
You have to create every query like you did ($q1,$q2,$q3,etc..) and make a final query like this:
$query->select("tab.*")
->from("(".$q1->union($q2)->union($q3).") as tab")
-&...
- 977
Only top scored, non community-wiki answers of a minimum length are eligible