HD SQL Select FF field type
Development and Programming
A simple field type extension for FieldFrame to create select menus from any custom SQL query. Unlike the method documented here, no core hacks are required 😊
A word of caution - please don’t use this extension unless you know how to write SQL. Please test the SQL first before creating a custom field. I don’t want anyone deleting their database by mistake!
So how can you use this? I would suggest to create one-to-many relationships between entries independent of the weblog/catgeory they are in. E.g. Offices>Staff, Staff>Department. There are specific instances where it may be preferable to use this method rather than categories or a related field: for example, if your entries represent categories.
I’m using it to generate a list of child pages for a given parent within a Structure module hierarchy. In my tree I have a parent leaf with entry id 30. To render it’s children in a select menu I use:
SELECT node.entry_id, entry.title
FROM exp_structure AS node
INNER JOIN exp_structure AS parent ON node.lft
BETWEEN parent.lft AND parent.rgt
LEFT JOIN exp_weblog_titles AS entry
ON node.entry_id = entry.entry_id
WHERE parent.lft >1
AND node.site_id = '1'
AND node.parent_id = '30'
GROUP BY node.entry_id
ORDER BY node.lftI hope this is useful to someone else.
I hope this is useful to someone else.
Absolutely will be yes!! 😊
Thanks for letting us all get a look at this one it looks like it will save a lot of headaches in the future.
Best wishes,
Mark
Dude. This fieldtype is the best thing that’s happened to me all MONTH. Thank you.
As a quick suggestion (to keep someone from “deleting their database by mistake”), you might try to limit it to only taking SELECT queries.
But surely the typical EE dev can be trusted not to hose their own site 😉
Seriously though, that’s a good idea. And I’m pleased you found it useful.
Okay, one request. Is there a way to make this multi-selection capable? It would save me about a zillion years if I could use it as a multiselect OR a single-select, per field.
Adrienne
Quick way to do this - line 71, change to:
$r = $DSP->input_select_header($field_name.'[]', 'y');Should work. But if you want to toggle between the two you would need to add an option in the extension configuration screen - this would be a bit more work. When I get some free time (ha!) I’ll add it in.
EDIT: sorry I lie, this won’t retain your selections. I would need to do some checking on the selected value to see if it is an array and iterate through it for each possible option. Doable but would take some time to work out I’m afraid.
If I just changed that one line, though, would I be able to loop through options like the native FF multi-select? Will it have the same sorts of tags?
This is awesome, thanks. I just spent (wasted) an hour trying to create my own custom fieldtype and this does everything I need and more.
This looks amazing, but I was trying to use this in an FF Matrix and it doesn’t seem to show up as an option. Has it been set up to use in FF Matrix?
Sorry, It’s not set up to work with FF Matrix. I may look into this at some point.