• Hello Fabrik Community

    Fabrik is now in the hands of the development team that brought you Fabrik for Joomla 4. We have recently transitioned the Fabrik site over to a new server and are busy trying to clean it up. We have upgraded the site to Joomla 4 and are running the latest version of Fabrik 4. We have also upgraded the Xenforo forum software to the latest version. Many of the widgets you might have been used to on the forum are no longer operational, many abandoned by the developers. We hope to bring back some of the important ones as we have time.

    Exciting times to be sure.

    The Fabrik 4.0 Official release is now available. In addition, the Fabrik codebase is now available in a public repository. See the notices about these in the announcements section

    We wish to shout out a very big Thank You to all of you who have made donations. They have really helped. But we can always use more...wink..wink..

    Also a big Thank You to those of you who have been assisting others in the forum. This takes a very big burden off of us as we work on bugs, the website and the future of Fabrik.

Pre filter googlemap viz

pie

Member
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
 
How do I do this?
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:
// 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:
  • Like
Reactions: pie
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:
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:
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
 
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:
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:
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
 
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:
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
 
Oh, yeah, duh, sorry wasn't thinking ... just move the case up a bit ...

Code:
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
 
Does this work for you? I am still getting the same error :(

thank you very much for your patience
 
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:
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:
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:
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
 
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
 
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
 
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!
 
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
 
Ahh I see! you already thought about that. Thanks!

Going back to a previous post,
You could use a query string variable for that, or a table field, rather than hard coding it.
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
 
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
 
We are in need of some funding.
More details.

Thank you.

Members online

Back
Top