Pre filter googlemap viz

Discussion in 'Community' started by pie, Nov 21, 2017.

  1. pie

    pie Member

    Level: Standard
    Hi, I'm sorry it's not the right forum but I just subscribed and I can't post in standard support forum yet. :)

    I have 3 tables
    places
    events
    places_events

    places contain lat & long values as well as address and other info
    events contain title, date and time
    places_events joins the previous two through 2 databasejoin elements (each event may happen in many places, and each entry contains place_id and event_id)

    I succesfully created a calendar viz and a googlemap viz:
    if I click on one event in the calendar, in my dreams I'd like to prefilter the googlemap viz and display only markers of places that in places_events have that event scheduled (inside a range within the user location).

    How do I do this?
    As a side question, is it possible to filter googlemaps via url?

    Thank you in advance
     
  2. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    Rather than just saying "you can't", which is the actual answer, looked in to this, as it's a kinda cool thing to do. I do something vaguely similarish on a couple of sites, where have a map viz displayed above a list (using a content plugin in the list intro), and a JS event that watches the list and fires a map reload when the list is filtered, so the map always shows the same things the list does. However, that's a lot simpler, because the map and the list are on the same table.

    The first issue with what you want to do is that the calendar viz currently doesn't let you hang any kind of JS events on a click, it just opens the selected entry. I added an event just now in this commit:

    https://github.com/Fabrik/fabrik/commit/7c93855ebff3da6309087a799fb33f227dc81178

    ... which lets you intercept an entry click, and abort the loading. It passes the viz object and the event object as params. So if you have a custom JS file, ./components/com_fabrik/js/viz+X (where X is the ID of your viz), you can do this:

    Code (Text):

    // defer running until Fabrik is loaded
    requirejs(['fab/fabrik'], function () {

        // now add the evet handler for the calendar click event
        Fabrik.addEvent('fabrik.viz.fullcalendar.clickentry', function (viz, entry) {

            // callback function for getCurrentPosition, will be fired if we succesfully get location
            function showPosition(position) {
                // we got a location, so open a link to the Fabrik list, providing the event ID clicked on, and the lat and lon
                // CHANGE LIST ID HERE
                window.open('index.php?option=com_fabrik&view=list&listid=2&resetfilters=1'
                    + '&event_id=' + entry.rowid
                    + '&lat=' + position.coords.latitude
                    + '&lon=' + position.coords.longitude
                );
            }

            // if we have HTML 5 geolocation, we're golden!
            // NOTE - most browsers (definitely Chrome) require a secure origin to use geolocation, eg. your must be using https for this to work
            if (navigator.geolocation) {
                var location = navigator.geolocation.getCurrentPosition(showPosition);
            }
            else {
               alert ("Boo hiss!  No geolocation.");
            }

             // return false to fullcalendar, so it doesn't pop up an event window
            return false;
        });
    });
     
    I tested this locally, and it works.

    That's the easy part. You now have a window opening on the list, with the event_id and the lat/lon on the query string. But to do the filtering, you'd have to build a prefilter that finds the events from the places_events table with matching event_id and in the radius ... which will be a fun query.

    I'll post this for now, for you to be getting on with, and come back with Part 2.

    Oh, and yes, you can filter viz's on the query string.

    -- hugh
     
    Last edited: Nov 22, 2017
  3. pie

    pie Member

    Level: Standard
    Thank you!
    I succesfully did almost everything you said, I have even written a dummy mysql query:
    I thought that (with your help.. ) I may "bind" the map visualization with the places table,
    inner join the places table with places_events where event = event_id and sort every place by a calculated distance.
    This is what my query does, I hope it would be possible to write it in list prefilters.

    I have an issue though: it seems that the js file is ignored.
    I succesfully updated Fabrik from github to take advantage of your commit and I added
    viz1.js file in /components/com_fabrik/js/
    1 is my calendar viz id, and the js contains exactly your code (my places list has id 2 )
    (I actually tried using any special char I could think of between viz and 1 - _ + ) however clicking on the event in the calendar visualization does not seem to do anything different if I have the file there or not: the standard popup window pops up.
    What should the js naming convention be? I assumed these rules should be valid
    http://fabrikar.com/forums/index.php?wiki/javascript/#javascript-location

    Thank you very much
     
    Last edited: Nov 22, 2017
  4. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    It should be viz_X.js, with an _

    Did you clear your browser cache? You probably have the old calendar JS cached, which doesn't fire the event. Easiest way is use Chrome, do ctrl-shift-I to bring up the debugged, then if you right click on the normal "Reload" icon, you'll have an option to "Empty cache and hard reload". The old F5 way doesn't seem to work any more.

    The prefilter query would need to be something like this:

    WHERE
    Field: id
    Condition: IN
    Value: SELECT id FROM places WHERE ...
    Type: Query

    And that inner query would need to select all ids from the places table (the one the filter is on) that match your criteria.

    And I think this is probably close to the query you need:

    Code (Text):

    SELECT
        p.id
    FROM
        places AS p
    INNER JOIN places_events AS pe ON pe.place_id = p.id
    WHERE
        pe.event_id = '{event_id}'
    AND (
        (
            (
                acos(
                    sin(({lat} * pi() / 180)) * sin((p.lat * pi() / 180)) + cos(({lat} * pi() / 180)) * cos((p.lat * pi() / 180)) * cos((({lon} - p.lon) * pi() / 180))
                )
            ) * 180 / pi()
        ) * 60 * 1.1515
    ) <= 10
     
    Note the {event_id}, {lat} and {lon} placeholders, which should get picked up from the query string. And the p.lat and p.lon field names, which you'll need to change to suit. I'm not sure if you'd need to quote the '{lat}' and '{lon}' ones. The problem with not quoting is if those names aren't on the query string, they get replace with nothing, and if no quotes, the query is invalid. But I'm not sure than doing math on an empty string doesn't error out anyway. So you'd probably need to do this on a copy of the list that ONLY gets used for this purpose.

    Also note the <= 10 on the end, which is the distance. You could use a query string variable for that, or a table field, rather than hard coding it.

    Also also note this is in miles, to make it Km, do *60*1.1515*1.609344.

    Also also also note - if you are using a Fabrik map element rather than separate lat and lon fields, so it's in a single field with (lat,lon):zoom, then there is some funky magic you can do to extract the lat and lon from that. So instead of using p.lat and p.lon, use ...

    SUBSTRING_INDEX(TRIM(LEADING '(' FROM p.map), ',', 1)

    ... and ...
    SUBSTRING_INDEX(SUBSTRING_INDEX(p.map, ',', -1), ')', 1)

    ... respectively.

    -- hugh
     
  5. pie

    pie Member

    Level: Standard
    Thank you! You're a wizard, that works perfectly :eek:
    You were right it was the browser's cache, useless f5!

    I have a new issue, which I believe comes from the prefilter:
    after choosing the event, the filtered list shows up, but if I click on the detail view for a place I get an sql error because the prefilter is still being applied and in this url I don't have the additional parameters which it is expecting (and there is no longer need for them, unless the user clicks the back button on his browser - on a second thought there is no longer need for the prefilter, but the user location may come in handy later to ask directions. ).
    #1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '} * pi() / 180)) * sin((p.lat * pi() / 180)) + cos(({lat} * pi() / 180)) * cos((' at line 36


    Can you think of a trick/prefilter setting to fix this?


    Thank you again
     
    Last edited: Nov 23, 2017
  6. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    OK, I think this might work.

    If the query string isn't there, then the placeholder won't get replace at all, it'll still be '{lat}', so we can leverage that and do a "CASE" in the WHERE, so if the placeholder hasn't been replaced, we just return all ids ...

    Code (Text):

    SELECT
        p.id
    FROM
        places AS p
    INNER JOIN places_events AS pe ON pe.place_id = p.id
    WHERE
        pe.event_id = '{event_id}'
    AND CASE
    WHEN '{lat}' LIKE '%lat%' THEN
       TRUE
    ELSE
        (
            (
                (
                    acos(
                        sin(({ lat } * pi() / 180)) * sin((p.lat * pi() / 180)) + cos(({ lat } * pi() / 180)) * cos((p.lat * pi() / 180)) * cos((({ lon } - p.lon) * pi() / 180))
                    )
                ) * 180 / pi()
            ) * 60 * 1.1515
        ) <= 10
    END
     
    So, if the '{lat}' placeholder hasn't been replaced, it'll still be '{lat}' in the query, so we can test for that ... and if so, just return all ids by using TRUE. If the '{lat}' in the WHEN has been replaced, it won't be LIKE '%lat%', so execute the radius query.

    -- hugh
     
  7. pie

    pie Member

    Level: Standard
    Thanks, unfortunately the issue persists (same error thrown)

    I suppose that the catch here is that I am still missing '{event_id}' on WHERE - which happens before the CASE:
    I am trying to understand how to add a similar condition on that, I felt it should have been easy at this point but I am unable to get it working: what am I missing?

    Code (Text):
    SELECT
        p.id
    FROM
        places AS p
    INNER JOIN places_events AS pe ON pe.place_id = p.id
    WHERE
    CASE
    WHEN '{event_id}' LIKE '%event_id%' THEN
    TRUE
    ELSE
      pe.event_id = '{event_id}'
    END
    AND CASE
    WHEN '{lat}' LIKE '%lat%' THEN
       TRUE
    ELSE
        (
            (
                (
                    acos(
                        sin(({ lat } * pi() / 180)) * sin((p.lat * pi() / 180)) + cos(({ lat } * pi() / 180)) * cos((p.lat * pi() / 180)) * cos((({ lon } - p.lon) * pi() / 180))
                    )
                ) * 180 / pi()
            ) * 60 * 1.1515
        ) <= 10
    END
    Thank you
     
  8. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    Oh, yeah, duh, sorry wasn't thinking ... just move the case up a bit ...

    Code (Text):

    SELECT
        p.id
    FROM
        places AS p
    INNER JOIN places_events AS pe ON pe.place_id = p.id
    WHERE
        CASE
    WHEN '{event_id}' LIKE '%event_id%' THEN
        TRUE
    ELSE
        (
            pe.event_id = '{event_id}'
            AND (
                (
                    (
                        acos(
                            sin(({ lat } * pi() / 180)) * sin((p.lat * pi() / 180)) + cos(({ lat } * pi() / 180)) * cos((p.lat * pi() / 180)) * cos((({ lon } - p.lon) * pi() / 180))
                        )
                    ) * 180 / pi()
                ) * 60 * 1.1515
            ) <= 10
        )
    END
     
     
  9. pie

    pie Member

    Level: Standard
    Does this work for you? I am still getting the same error :(

    thank you very much for your patience
     
  10. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    I can't tell you if that specific query works, as I'm just making it up, but I did test the basic theory of the placeholder replacement with a simpler case:

    Code (Text):

    SELECT id FROM fab_main_test WHERE CASE WHEN '{lon}' LIKE '%lon%' THEN TRUE ELSE id < 100 END
     
    ... and without &lon=123 on the query string, the query looks like ...

    Code (Text):

    WHERE ( fab_main_test.id IN (SELECT id FROM fab_main_test WHERE CASE WHEN '{lon}' LIKE '%lon%' THEN TRUE ELSE id < 100 END)
     
    ... and with &lon=123 ...

    Code (Text):

    WHERE ( fab_main_test.id IN (SELECT id FROM fab_main_test WHERE CASE WHEN '123' LIKE '%lon%' THEN TRUE ELSE id < 100 END)
     
    ... and it returns the right results.

    So I know the basic theory works - that if the query string arg isn't there, the placeholder gets left in the query and we can use it in a CASE.

    Append &fabrikdebug=1 to your URL (make sure you enable debug mode in the global Fabrik options), then look at the list getData query. That should show you exactly what is happening.

    -- hugh
     
  11. pie

    pie Member

    Level: Standard
    It seems right to me, thanks for the additional explanation. Maybe the issue is coming from elsewhere.
    I noticed that removing the prefilter query from the list I get another kind of error clicking on details view:
    anyway the map works fine in list view.

    Warning: file_get_contents(https://maps.google.com/maps/api/st...3646,9.191926599998988&sensor=false&key=mykey): failed to open stream: HTTP request failed! HTTP/1.0 403 Forbidden in /libraries/fabrik/fabrik/Helpers/Image/Image.php on line 187



    Thank you
     
  12. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    Does it literally say mykey in the Google url, or did you replace that?

    Sent from my HTC6545LVW using Tapatalk
     
  13. pie

    pie Member

    Level: Standard
    nono, I replaced it :)
     
  14. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    What do you get if you try and open that link with your actual key.

    -- hugh
     
  15. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    A 403 from that maps API almost always means your key is invalid. I don't think I've ever seen any other reason for it.

    -- hugh
     
  16. pie

    pie Member

    Level: Standard
    Ah, you're right! I got the error and it was not related.
    I had the static map portion of the google api disabled on google side, I thought it was enabled by default together with the others (non static maps were already working).
    However, does this mean that the the googlemap element makes its request to google even if it's set to not visible? It would be covenient if it could avoid it to save resources.

    p.s. I got the stupid issue: I was missing ' ' around lat & lon placeholders in the distance calculation. but in my defense I can say it was working in the list view... :)
    Thank you for all your help!
     
  17. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    We still render things that aren't shown in list view, because the might be used in other ways, like in group templates, or Calc elements, etc. You can turn this behavior off on the Element setting, turn "always render" off.

    Sent from my HTC6545LVW using Tapatalk
     
  18. pie

    pie Member

    Level: Standard
    Ahh I see! you already thought about that. Thanks!

    Going back to a previous post,
    I'd like to provide a default value for distance/range, which may be changed by the user, and an additional googlemap/address element to filter the list by a specified location (not browser choice but user choice).

    Can you tell me if this approach makes sense?
    1 create a "filters" list where to put all of my default values and fields (range, googlemap element for user chosen position)
    2 insert this form via content plugin before the filtered map
    3 intercept "filter" table updates to update the url (and refresh the page) with a js trick (form_X) like the one you used before


    Thank you again
     
  19. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    Are you trying to apply this to the same map, or is this a different page?

    -- hugh
     
  20. pie

    pie Member

    Level: Standard
    The same one,
    I figured that since I don't have elements in places table to represent user location and search range (and doesn't make sense to have them) I could "fake" them creating another list which doesn't store values in the db (except the default values) but can be modified by each user at his/her will to tweak the query.
    This was just a thought, maybe is impossible to do this, or you already have a better option to suggest

    thank you
     

Share This Page