Hot answers tagged

2

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 submission data. Please note, while you may only have one "form" in your #__rsform_submissions table, it would be more robust to specify the FormId in the WHERE ...


2

From this sample data: INSERT INTO `o2pe0_rsform_submission_values` (`SubmissionValueId`, `FormId`, `SubmissionId`, `FieldName`, `FieldValue`) VALUES (2124732, 23, 47529, 'SHELTER', 'Bellville'), (2124728, 23, 47529, 'Amount', '500'), (2124731, 23, 47529, 'ClientID', 'G993HJOS'), (2124729, 23, 47529, 'Monthpayment', '2020-07-09'), (2124743, 23, 47530, '...


2

To debug the issue: $query is an object that generates the SQL statement to be executed. Then, you can echo $query->__toString() and compare the outputs. In a complex case, it may be necessary to generate sub-queries to create a final big query.


2

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 now, but wanted to try to help. If it doesn't work, let me know in comments and I will try to adjust:SELECT a.FieldValue as Container, b.FieldValue as ...


1

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: (DB Fiddle Demo) value field_id value field_id id title alias introtext fulltext catid field_id item_id value documentary 417 2021-05-05 46 5 test 5 test-5 ...


1

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" queries have data. I am assuming that %%P1=0%% is your Plotalot placeholder. This was a late-night endeavour, so I cannot guarantee that it is fully refined, ...


1

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 = $db->getQuery(true) ->select( array( "MAX(CASE WHEN v.FieldName = 'Team_Name' ...


1

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 v.FieldValue END) as Team_Name, MAX(CASE WHEN v.FieldName = ".$db->q('Manager_Name')." THEN v.FieldValue END) as Manager_Name")->from('#__team_values as v'...


1

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 WHERE clause will disqualify rows before grouping occurs. In this case, you only want to focus on data in the scope of FormId 14, so this should go in your ...


1

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 sql to extract a single article's custom values and assign meaningful column aliases you can use a technique called a pivot -- it effectively "rotates" the ...


1

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: create a result set of containertype1 values sorted by SubmissionId for each SubmissionId from FormId 28 including only submissions that contain a Status1 value of ...


1

Here you are: $query->select($db->qn('s.SubmissionId','id')) ->select($db->qn('s.DateSubmitted','date')) ->select($db->qn('sv.FieldValue','status')) ->select($db->qn('nv.FieldValue','name')) ->select($db->qn('ev.FieldValue','email')) ->from($db->qn('#__rsform_submissions','s')) ...


1

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 columns -- this means tripling the number of rows in your table. While this new structure looks less condensed, it permits you to perform much more clean/direct ...


1

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") ->order("tab.orderingField"); It's well explained in this article: https://docs.joomla.org/Using_the_union_methods_in_database_queries#Ordering_results


Only top voted, non community-wiki answers of a minimum length are eligible