db join autocomplete for location. I need consult.

vipzeus

vipzeus

Member
Hi everybody

In my new site (J!2.5 & Fabrik 3) im trying to create the "user details" form where i have some fields as bellow:

userid
country ->dbjoin with all countries
itcity ->dbjoin with all italian cities (about 17000 records)

With javascript condition itcity field appear only if from countries italy is selected and hidden if not.

The same method i have to create for other countries, each table for each country.

This method works great as is fasted to divide the cities records on more tables.

The problem is in my "user details" table with this method i'll have for each user all cities table, itcity, frcity, encity, etc...

Question:
Can i leave only one field (Cities) where records with all world cities (about 6.000.000 records) is available for all users? The table has many informations as Country;Language;ID;ISO2;Region1;Region2;Region3;Region4;ZIP;City;Area1;Area2;Lat;Lng;TZ;UTC;DST. I would like to use this table with CONCAT as autocomplete dbjoin.
is it fast way? or stressed for my site?
 
Hi Felixkat and thanks for your answer.
Yes, i seen this example but its not what im looking for.
Maybe the best solution its the first one, to have for each user all citties table available.
 
Im not sure what the difference is between what you describe and what Felixcat suggested in using the cascadingdropdown.

Can i leave only one field (Cities) where records with all world cities (about 6.000.000 records) is available for all users? The table has many informations as Country;Language;ID;ISO2;Region1;Region2;Region3; Region4;ZIP;City;Area1;Area2;Lat;Lng;TZ;UTC;DST. I would like to use this table with CONCAT as autocomplete dbjoin.
is it fast way? or stressed for my site?
Click to expand...

This will be very slow, building a dropdown list of 6000000 items is probably not a good idea!

I would really suggest using the autocomplete element - its designed for exactly this situation.
 
Yeah, I'd probably go with the autocomplete option on a join element, using a single table, and maybe a WHERE filter clause that applies the selected value of the 'country' element (using a placeholder).

-- hugh
 
Thanks for the suggest,

Yeah, I'd probably go with the autocomplete option on a join element, using a single table, and maybe a WHERE filter clause that applies the selected value of the 'country' element (using a placeholder ).
Click to expand...

Something like this?
WHERE {thistable}.Country = (SELECT country.id FROM {thistable}.country)

I have some errors with that.
 
oh and generally the sub select would select from another table? seems you are selectign from the same table so you're asking for all countries which have a country name equal to its id?
 
what are the errors you get?
Click to expand...
SELECT command denied to user 'vipzeus_1'@'localhost' for table 'country' SQL=SELECT DISTINCT(`GeoPC_ITALY`.`ID`) AS value, CONCAT(GeoPC_ITALY.City,' ',GeoPC_ITALY.ZIP,' ',GeoPC_ITALY.Region3,' ',GeoPC_ITALY.Region1,' ',GeoPC_ITALY.ISO2) AS text FROM `GeoPC_ITALY` AS `GeoPC_ITALY` WHERE GeoPC_ITALY.Country = (SELECT country.id FROM GeoPC_ITALY.country) AND `GeoPC_ITALY`.`ID` IN ('11444') ORDER BY text ASC
Click to expand...
oh and generally the sub select would select from another table?
Click to expand...
Yes, from the userdetails_countries dbjoin element as dropdown

seems you are selectign from the same table so you're asking for all countries which have a country name equal to its id?
Click to expand...
:confused: I m litle comfused then.
At the same form i have the dbjoin ( userdetails___country  )from the table countries where value=id and label=country

The other dbjoin as autocomplete is (userdetails___city_it) from the table GEOPC where value=id and CONCATlabel= {thistable}.City,' ',{thistable}.ZIP,' ',{thistable}.Region3,' ',{thistable}.Region1,' ',{thistable}.ISO2

At the table GEOPC i have a column "Country" ??which values correspond the id from countries table.
 
You'r subquery:

WHERE {thistable}.Country = (SELECT country.id FROM {thistable}.country)
Click to expand...
With that where statement you're subquery select should return only one record, so you will need to add a where statement to it for it to work

Alternatively try changing it to:

Code: 
WHERE {thistable}.Country IN (SELECT country.id FROM {thistable}.country)

I m litle comfused then.
Click to expand...

me to, can we take a step back here.

Were the database join and cascading dropdown working before you tried to add the where statement?

If not that is what you need to do first.

If yes, then can you post a design of your tables, a link to the form and a description of what exactly it is that you want the where statement to do. Currently what you have doesn't make any sense to me, but I'd need to know what you needed filtering from the list to advise further

-Rob
 
I was thinking more along the lines of ...

WHERE {thistable}.Country = '{yourtable___yourcity_raw}'

... or whatever your city element is called.

-- hugh
 
Were the database join and cascading dropdown
Click to expand...

I have not cascading dropdown in my form. I was tried and is very slow.

Anyway, with cascading was working with:
Render as: autocomplete
Connection: site database
Table: GeoPC
id: Country
Label: City (Very slow render data)
Concat: {thistable}.City,' ',{thistable}.ZIP,' ',{thistable}.Region3,' ',{thistable}.Region1,' ',{thistable}.ISO2

Watch element: Country
Foreign key: Country

What i'm looking to do is use databasejoin (NOT Cascading dropdown) for GeoPC but with some prefilter watching the other databasejoin Country.

Is it possible that?
In GeoPC i have about 20000 records for the moment and its very fast as autocomplete but with 6000000 (where all world localities) i need to filter them before user select
 
Well, it depends on whether you need the GeoPC dropdown to update "live" on your page when you change the Country selection.

If so, then no, you can't do that with a join element. or at least, I don't think you can. I think I'm going to need to get my hands on your site, to see what we can do.

What is the maximum number of options you would expect to have being displayed once a City is selected, with your full database?

-- hugh
 
Its the united kingdom with 1750611 records.
Second is Canada with 839429 and third Brasile with 764276.
Skype me when have time :)
Thanks
 
AH yes, half a million entries in a select list would be slow! Have you tried rendering the cascading dropdown as an auto-complete list? That would speed things up
 
I tried as autocomplete

Anyway, with cascading was working with:
Render as: autocomplete
Connection: site database
Table: GeoPC
id: Country
Label: City (Very slow render data)
Concat: {thistable}.City,' ',{thistable}.ZIP,' ',{thistable}.Region3,' ',{thistable}.Region1,' ',{thistable}.ISO2

Watch element: Country
Foreign key: Country
Click to expand...

As dropdown mozilla go in crash
 
Yeah, no way a dropdown of that size will render.

OK, so the problem is that it's slow when you render it as autocomplete? Do you mean slow when loading the page initially, or when building the autocomplete choice menu when typing in the field?

-- hugh
 
Do you mean slow when loading the page initially, or when building the autocomplete choice menu when typing in the field?
Click to expand...

Both cases, first of all when user choose the country.
 
Something else, as databasejoin element is very fast because nothing is loaden before user digit any letter.
But i would like to filter the results for this reason.
if user select the country (Italy for example) the results of cities database must contain only the italy's cities
 
solution in post #11 = autocomplete + placeholders is it working ?
i can not make it work with placeholders
 
You must log in or register to reply here.

Useful links

  • Create a Fabrik Account (Downloads)
  • Create a Forum Account
  • Fabrik Wiki
  • Fabrik 4 Changelog /Install /Upgrade
    • We are in need of some funding.
    More details.

    Thank you.

    Staff online

    Members online

    Total: 183 (members: 2, guests: 181)
    Back
    Top