query module, sql created via plugin (and other questions :) )
Development and Programming
Hello - I’m experimenting with the query module and have a few questions with issues I’m running into:
1) Is it possible to generate the SQL via a plugin, so it would look something like this:
{exp:query sql="{exp:makemydamnsqlalready:query param='something'}" limit="10" pagination="bottom"}
...
{/exp:query}I ask if it’s possible as when I attempt this, I don’t receive the “{no_results}” content, but I don’t get any listings either (!). Note that the SQL the “makemydamnsqlalready” plugin DOES generates does give results - I can copy and paste the string and get results without issue:
{exp:query sql="SELET * FROM `hollywood` WHERE `shallow` = 1" limit="10" pagination="bottom"}
...
{/exp:query}However, I need some SQL to change depending on that param=’something’ parameter (As I’m attempting to use the query module in conjunction with Low_seg2cat).
The “makemydamnsqlalready” doesn’t seem to return an error, as I can use that module tag separately and see the SQL code generated in the browser.
2) Image fields with the query module - I am getting the image fields back as expected when I run the SQL, but the it contains tags for {file_dir} which may not be getting parsed correctly/at all. Is this a known limitation of the query module or am I possibly doing something wrong?
thanks
Moved to Development and Programming by Moderator
According to this thread: http://ellislab.com/forums/viewthread/167353/ It looks like I will need to do some extra work to output the file directory.
I’m hoping someone has some input on generating SQL via another plugin to use in the query module.
Thanks
UPDATE:
I’ve been debugging this by hacking into the Query module (Keep in mind this is 2.0.1 beta build - perhaps this was a fixed bug….):
I echo’ed out the SQL (which returns valid SQL, as I can add it into phpmyadmin and get expected results). I then print_r the resulting $query object, and get the following:
//Sample bit of SQL that's echo'ed
SELECT `exp_channel_titles`.`entry_id`, `exp_channel_titles`.`channel_id`, `exp_channel_titles`.`ip_address`, `exp_channel_titles`.`title`.... ASC LIMIT 0, 10
//The print_r of the query result object
CI_DB_mysql_result Object
(
[conn_id] => Resource id #37
[result_id] => Resource id #133
[result_array] => Array
(
)
[result_object] => Array
(
)
[current_row] => 0
[num_rows] => 10
[row_data] =>
)Note that it says there are 10 resulting rows, but the the result_array and result_object arrays are empty!
Here is the SQL:
SELECT `exp_channel_titles`.`entry_id`, `exp_channel_titles`.`channel_id`, `exp_channel_titles`.`ip_address`, `exp_channel_titles`.`title`, `exp_channel_data`.`field_id_54` as series, `exp_channel_data`.`field_id_56` as model, `exp_channel_data`.`field_id_68` as listing_image, `exp_channel_data`.`field_id_58` as msrp
FROM `exp_channel_titles`
LEFT JOIN `exp_channel_data` ON `exp_channel_titles`.`entry_id` = `exp_channel_data`.`entry_id`
WHERE `exp_channel_titles`.`channel_id` = 8 AND `exp_channel_titles`.`site_id` = 1
AND (`exp_channel_titles`.`status` = 'open' OR `exp_channel_titles`.`status` = 'featured')
ORDER BY FIELD(series,'Series3','Series1','Series2') ASC LIMIT 0, 10Now the point of all this is to order the “series” field in a custom order. This works when the SQL is added to phpMyAdmin.
I also want to reiterate that when the query is directly outputted into the module like so: {exp:query sql="SELECT *.."}…{/exp:query}, I do get results. (Not sure how that is messing up when the query is generated via a plugin….)
Any thoughts? I’ve added a screenshot of the code within the query module (mod.query.php) where I echo the SQL and then print_r the strange result object.
dstechroom,
You will need to upgrade to most recent non-beta version of EE
I am not quite sure what you are attempting to do in the first place. Can go go into more detail what you wish to achieve.
Note, you can wrap or nest query tags inside or outside other tags.
{exp:makemydamnsqlalready:query param='something'}
{exp:query sql="SELET * FROM `hollywood` WHERE `shallow` = 1" limit="10" pagination="bottom"}
...
{/exp:query}
{/exp:makemydamnsqlalready:query}Thanks John - That could be handy in debugging this.
I didn’t see any bug fixes specifically related to this in the version docs, however I’m in the process of updating this site build and will see if that helps.
Very good. Let us know how you progress, please.
Hello -
I’ve updated the site to the latest version of EE and unfortunately it still is not working.
To be clear, I’ve tried:
//Works, but not dynamic enough for business logic needs
{exp:query sql="SELET * FROM `hollywood` WHERE `shallow` = 1" limit="10" pagination="bottom"}
...
{/exp:query}//Still doesn't work
{exp:query sql="{exp:makemydamnsqlalready:query param='something'}" limit="10" pagination="bottom"}
...
{/exp:query}//Tried this without success. {query} tag DOES pump put the correct SQL (outputted it separately to test)
{exp:makemydamnsqlalready:query param='something'}
{exp:query sql="{query}" pagination="bottom"}
...
{/exp:query}
{/exp:makemydamnsqlalready:query}Barring any further possible feedback on why this may be happening (I’m very confused, especially after the results of hacking into the query module), I may resort to having some PHP in the template - However, I’m worried about running into a conflict with PHP in input vs output in that case.
Thanks all for your help! (Any further suggestions?)
dstechroom,
Can you elaborate what’s in this plugin please?
{exp:makemydamnsqlalready:query param='something'}Wrapping either inside or outside the query tag is the way I would suggest but it depends what you have inside your plugin. It may be a simple case of parsing as it stands already
Absolutley! The plugin does appear to be outputting the SQL, as I have added it directly into the template, and then copied and pasted and tested the SQL to make sure it didn’t errors.
Here is what’s going on within the plugin: Note: This current iteration assumes that the {exp:query} tags are wrapped inside of my plugin tags as per your (John’s) suggestion.
function query() {
$categories = $this->EE->TMPL->fetch_param('category');
$variables = array();
if(empty($categories)) {
$query = "SELECT `exp_channel_titles`.`entry_id`, `exp_channel_titles`.`channel_id`, `exp_channel_titles`.`ip_address`, `exp_channel_titles`.`title`,
`exp_channel_data`.`field_id_54` as series, `exp_channel_data`.`field_id_56` as model, `exp_channel_data`.`field_id_68` as listing_image, `exp_channel_data`.`field_id_58` as msrp
FROM `exp_channel_titles`
LEFT JOIN `exp_channel_data` ON `exp_channel_titles`.`entry_id` = `exp_channel_data`.`entry_id`
WHERE `exp_channel_titles`.`channel_id` = 8
AND `exp_channel_titles`.`site_id` = 1
AND (`exp_channel_titles`.`status` = 'open' OR `exp_channel_titles`.`status` = 'featured')
ORDER BY FIELD(series,'series3','series1','series2') ASC";
$variables[0] = array('query' => $query);
} else {
$cats = explode('&', $categories);
$str = " AND (";
foreach($cats as $c) {
$str .= "`exp_category_posts`.`cat_id` = ".$c." OR ";
}
$str .= ")";
$str = str_replace('OR )', ')', $str);
$query = "SELECT `exp_channel_titles`.`entry_id`, `exp_channel_titles`.`channel_id`, `exp_channel_titles`.`ip_address`, `exp_channel_titles`.`title`,
`exp_channel_data`.`field_id_54` as series, `exp_channel_data`.`field_id_56` as model, `exp_channel_data`.`field_id_68` as listing_image, `exp_channel_data`.`field_id_58` as msrp,
`exp_category_posts`.`cat_id`, `exp_categories`.`cat_url_title`
FROM `exp_channel_titles`
LEFT JOIN `exp_channel_data` ON `exp_channel_titles`.`entry_id` = `exp_channel_data`.`entry_id`
LEFT JOIN `exp_category_posts` ON `exp_category_posts`.`entry_id` = `exp_channel_data`.`entry_id`
LEFT JOIN `exp_categories` ON `exp_category_posts`.`cat_id` = `exp_categories`.`cat_id`
WHERE `exp_channel_titles`.`channel_id` = 8
AND `exp_channel_titles`.`site_id` = 1".
$str."
AND (`exp_channel_titles`.`status` = 'open' OR `exp_channel_titles`.`status` = 'featured')
ORDER BY FIELD(series,'series3','series1','series2') ASC";
$variables[0] = array('query' => $query);
}
$this->return_data = $this->EE->TMPL->parse_variables($this->EE->TMPL->tagdata, $variables);
return $this->return_data;
}//Still doesn't work {exp:query sql="{exp:makemydamnsqlalready:query param='something'}" limit="10" pagination="bottom"} ... {/exp:query}
No, you’re probably running into parse order issues here. Combing the query module with a plugin is rather unusual, I’d say. If you are usign a plugin already just make the SQL lookup part of its output.
–Edited–
Ingmar, that makes sense, except that I can go into the query module and do a var_dump to see that the query does, in fact, reach the module.
It returns a DB result object that says it has 10 results (the num_results variable is set to “10” when I set limit=”10”) but the results array is empty!
Other updates, since I’ve had more time to dig since I’ve updated to 2.1 (Please bear with me 😊 )
//There is proper SQL outputted here
<!--
{exp:makesql:query}
-->
//not sure whats happening here yet
{exp:query sql="{exp:makesql:query}" limit="10" pagination="bottom"}
...
{/exp:query}So, I am hoping this worked, but when I went into the query module and echo’ed out the $sql parameter output, I receive the following:
//mod.query.php
echo $this->EE->TMPL->fetch_param('sql'); //outputs M50o93H7pQ09L8X1t49cHY01Z5j4TT91fGfr
die();Not exactly sure what that hash is? Is it attempting to parse {exp:makesql:query} as a PHP variable and returning a chunk of memory?
dstechroom, we’re rapidly approaching coding questions, so moving this thread to the Development forum.
I would say.. try this:
{exp:query sql="{exp:makemydamnsqlalready:query param='something'}" limit="10" pagination="bottom" parse="inward"}
...
{/exp:query}See the parse=”inward” parameter?