Hi, I’ve been two days trapped in a wrong SQL query generated by Views.
I’m using:
Drupal 6.4
Views 6.x-2.x-dev
Location 6.x-3.0-test3
Location Views 6.x-3.0-test3
GMap 6.x-1.0-beta6
GMap Location 6.x-1.0-beta6
I understand that early versions are problematic; however these are pretty much the version available at this moment.
The error shows as follow:
I’m trying to create a map to show the location of users of my site. I’m creating a view (using Views module) that would pull data from tables users and location and would present the information in a GMap block. I have tried every possible configuration and the module Views always shows the following error.
user warning: Unknown column 'location.latitude' in 'field list' query: SELECT DISTINCT(users.uid) AS uid, users.created AS users_created, users.name AS users_name, users.picture AS users_picture, location.latitude AS gmap_lat, location.longitude AS gmap_lon FROM enc_users users LEFT JOIN enc_location_instance location ON users.lid = location.lid LIMIT 0, 10 in /home/weekend2/public_html/encuentrocubano.com/sites/all/modules/views/includes/view.inc on line 723.
According to this error and by looking into the database I found that table users and table location are linked using a match table called location_instance.
The problem that I see is that Views is not generating the right SQL query because it is not connecting the index fields as it should. Here is the query:
SELECT DISTINCT(users.uid) AS uid,
users.created AS users_created,
users.name AS users_name,
users.picture AS users_picture,
location.latitude AS gmap_lat,
location.longitude AS gmap_lon
FROM enc_users users
LEFT JOIN enc_location_instance location ON users.lid = location.lid
I went to MyAdmin and I was able to create a query that would give me the expected results with no error. Here it is:
SELECT DISTINCT (users.uid) AS uid,
users.created AS users_created,
users.name AS users_name,
users.picture AS users_picture,
location.latitude AS gmap_lat,
location.longitude AS gmap_lon
FROM enc_users users
LEFT JOIN enc_location_instance location_instance ON users.uid = location_instance.uid
LEFT JOIN enc_location location ON location_instance.lid = location.lid
The people who made this last version of Views have done a wonderful job by helping the non-programmer to create queries without knowing SQL language. I would like to suggest having a box to plug a customized query.
Last but not least, I wonder if there is any way, in the meantime, of replacing (perhaps within the database) the Views generated query by a “hand-made” one. Could anyone point me in the right direction?
Thanks!
Views export:
$view = new view; $view->name = 'Test_GMap_View'; $view->description = 'Test GMap View'; $view->tag = ''; $view->view_php = ''; $view->base_table = 'users'; $view->is_cacheable = FALSE; $view->api_version = 2; $view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */ $handler = $view->new_display('default', 'Defaults', 'default'); $handler->override_option('fields', array( 'created' => array( 'label' => 'Created date', 'date_format' => 'small', 'custom_date_format' => '', 'exclude' => 0, 'id' => 'created', 'table' => 'users', 'field' => 'created', 'relationship' => 'none', ), 'name' => array( 'label' => 'Name', 'link_to_user' => 1, 'exclude' => 0, 'id' => 'name', 'table' => 'users', 'field' => 'name', 'relationship' => 'none', ), 'picture' => array( 'label' => 'Picture', 'exclude' => 0, 'id' => 'picture', 'table' => 'users', 'field' => 'picture', 'relationship' => 'none', ), )); $handler->override_option('access', array( 'type' => 'none', )); $handler->override_option('title', 'Default GMap page'); $handler->override_option('use_ajax', TRUE); $handler->override_option('distinct', 1); $handler->override_option('style_plugin', 'gmap'); $handler->override_option('style_options', array( 'grouping' => '', 'macro' => '[gmap ]', 'datasource' => 'location', 'markers' => 'static', 'markertype' => 'big blue', )); $handler = $view->new_display('page', 'Page', 'page_1'); $handler->override_option('path', 'listadecubanos.html'); $handler->override_option('menu', array( 'type' => 'none', 'title' => '', 'weight' => 0, )); $handler->override_option('tab_options', array( 'type' => 'none', 'title' => '', 'weight' => 0, )); $handler = $view->new_display('block', 'Block GM from JC', 'block_1'); $handler->override_option('block_description', 'Block GM from JC'); $handler->override_option('block_caching', -1);
Comments
Comment #1
merlinofchaos CreditAttribution: merlinofchaos commentedSecond, your export is corrupted because your wysiwyg doesn't understand code and tried to translate the text.