By guymandude on
I need to be able to query the Drupal database with sql. I thought I would be clever and use the Views generated query where I could, but the Views query cannot be copy/pasted into an SQL query tool like PHPMyAdmin. I have tried the query using little chunks at a time but at some point it just starts throwing errors. Is there a tool that will convert Views query syntax to regular MySQL query language?
Appreciate your thoughts and if you can help me get the correct mysql query string I would be grateful.
This Views displays all nodes of a content type called endpoint with a text field called field_ct_endpoint_type. The endpoint type I am filtering on is 'server'.
|
Views |
SELECT node.title AS node_title, node.nid AS nid, node.created AS node_created
FROM
{node} node
INNER JOIN {field_data_field_ct_endpoint_type} field_data_field_ct_endpoint_type_value_0 ON node.nid = field_data_field_ct_endpoint_type_value_0.entity_id AND (field_data_field_ct_endpoint_type_value_0.entity_type = 'node' AND field_data_field_ct_endpoint_type_value_0.deleted = '0' AND field_data_field_ct_endpoint_type_value_0.field_ct_endpoint_type_value = 'server')
WHERE (( (node.status = '1') AND (node.type IN ('endpoint')) AND( (field_data_field_ct_endpoint_type_value_0.field_ct_endpoint_type_value = 'server') )))
ORDER BY node_created DESC
LIMIT 5 OFFSET 0 |
Comments
I need to be able to query
What is the use case? Maybe we can do it, for instance, with the "entity.query" service.
What’s up Wombat? Coming to
What’s up Wombat? Coming to my rescue again!
I don’t know what entity.query is. I’m building a dashboard the pull data from various fields across many content types to create a bunch of graphs. I’m using Grafana cuz I don’t want to take the time to learn how to create a custom module (l am no coder by any stretch). Grafana uses regular SQL queries, so here I am now having to figure it all out. Figuring out some SQL queries is going to be easier and quicker than figuring out how to build a module.
I used Views to get an idea of a SQL query but it won’t paste as-is into the phpMyAdmin query tool. I have tried cleaning up the curly braces and other potential syntax problems but have not figured out the correct query yet.
I need to select all nodes that are of content type endpoint where the endpoint type field value is server. Once I figure this one out I can use it over and over for other endpoint types. It will also help me figure out other queries I haven't started working on yet.
Happy Holidays mate.
Alternatively, I suggest to
Alternatively, I suggest to create the REST Export with Views and using the following Grafana's plugins:
SimpleJson
Grafana Infinity Datasource.
Thanks for those suggestions,
Thanks for those suggestions, I'll stand up a D8 box and give that REST stuff a shot. BTW, I'm still using D7 because I love the Field Formatter CSS Class module which is not available in D8. If you recall, you helped me get that working a while back. I would love to be able to move this project to D8/9 if there is a way to achieve similar results.
Here's a link to that thread: Link
Here's a link to the module: Link
I reread our past solution
I reread our past solution and it seems it not required the 'Field Formatter CSS Class' module, because we used the standard List field.
You need to convert the query by using table prefixes
{node} and {field_data_field_ct_endpoint_type} need to be converted to their physical name in the database.
When not using table prefixes you can just remove {}. The resulting sql query should be possible to run in phpMyAdmin.
Thanks for your feedback
Thanks for your feedback mmjvb. I have used this technique before (using Views to get the SQL syntax) and found out the curly braces needed to be removed to get it to work. I could have sworn I tried it this time before opening this case, but I just did it and it worked!
I feed like a knucklehead...lol.
BTW I also just realized that query is not exactly what I wanted. I wanted the count of rows that query returned. Back to the drawing board...
Most common to not have table prefix
Table prefix is normally used for having multiple data sets in the same database. Allowing for multiple sites where there is a limit on the available databases. Today, people choose to have a database per site.
For a count of rows just replace SELECT .... with SELECT COUNT(nid)
You should not need the ORDER BY but it doesn't need removal. Same thing for the LIMIT line.
Remove double quotes
I had double-quotes in my "Views query" output. I needed to remove the curly braces and the double quotes in order to run the sql query.
Just a heads up to others who may find themselves here