Field Type from DB and by AJAX

[Pete]

Hi folks

Just a couple of quick queries regarding field types.

Firstly, is it possible to have a select dropdown field where it gets the select options from the results of a database query? If so, could someone point me in the right direction as to how to go about it?

And secondly, for datasets that are quite large you could end up with a MASSIVE list the above scenario becomes less practical, so has anyone thought about how you might do an Ajax input field whereby you start typing someone's name for example and it does one of those nifty autocomplete thingys: http://docs.jquery.com/Plugins/autocomplete - the obvious issue here is how you then save the key (in this case I guess the user ID in the external database) to that field.

These are just tow examples I can think of that would be very handy if they don't already exist, and in both cases I'm thinking specifically of an example where it's querying a non-PW database (trying to make different systems play together - specifically I'd like to query some fields from forum software if possible).

[apeisa]

Ryan has mentioned that he will develop autocomplete inputfield at some point, though no deadlines for this. Of course if someone has need for it before, all free to build

But your another question: getting values from another database. Yes, there is interesting question on how to save those values in pw database. If you are ok to save just the value as text, then you are good to go with text field and create new inputfield for it (which queries your database and offers those in select field or autocomplete). There isn't key + value fieldtype (yet, I have been thinking of building one), so if you need that functionality then you need to create a custom fieldtype also.

You can also use multiple fields and pages to store values. If you choose this route, then you have to build little script that imports data from your external database to pw fields. I would probably do this route myself, since it is pretty easy to do and then it's all "native" on processwire side.

Quick example: let's say you want to pull tags from another database and then use those exactly same tags in your processwire posts. You create template called tag. It has only one field, title. Then you create page that has those tags as children (ie. /tags/). Now your import script would do the following:

• Query external database
  
• Loop all the values
  
• Save all the values as tags, under the /tags/ page
  
• If there is already tag with same name, then skip
  
• If you want to also remove tags (in case the tag is removed from external db), then it is little bit more complicated (you have to add some flag to all tags, and remove flag if tag is still there => after loop remove tag pages that still have flag on them)
  
• If you do remove tags, then you need to remove all relations that is already made to it (probably before you remove tag)
  

After import is working, you just set the page field like you do with any other relation on processwire. There is many different ways to allow this selection (radios, checkbox, select, asmSelect etc), so just choose your favourite. Then you add to that field to templates where you want to use it (since we are talking about tags here, you would probably add your tags-field to your "post" template).

Tags was probably bad example for this latter method, since it requires only one field. But if importing something more complicated, like users with multiple attributes (name, phone, email, password etc), then this is actually pretty good method imo.

Last point: you probably want to cron your import script. There is LazyCron if you don't want or can't setup real cron: http://processwire.com/talk/index.php/topic,284.0.html

[Pete]

I think in the first instance what I'm going to be doing is running a PW site alongside Invision Power Board.

In this case I don't have to worry about duplicating member names and IDs in PW as they'll never change in the forum software, so literally all I need to be able to do is start typing a member's name and have autocomplete do the hard work of getting username and ID from the IPB database, storing the user ID in a plain text field in PW.

I'll have a tinker and see what I can come up with, but it might well take me some time to get used to PW since I only downloaded it yesterday

[ryan]

For the autocomplete – I'm going to finish the new ProcessSearch module first, because that will provide the JS ajax results for the autocomplete. Luckily, that part is almost done.

If you are querying something else for your autocomplete, I think you'll find it relatively simple to implement if you use jQuery UI's autocomplete. PW admin is already including jQuery UI, so that widget is already available to the admin.

As for selecting from a massive list that needs to be dynamic, have a look at the InputfieldPageListSelectMultiple module, which can be enabled from your Modules tab. Here is a video of how it works:

http://processwire.com/videos/page-fieldtype/

Though I'm thinking this isn't what you need because this only works for selecting pages.

[Pete]

Wow - thanks ryan, that video looked awesome - as you say not exactly what I need just now but every new thing I see about ProcessWire gives me ideas for use in other projects and makes me wonder why other CMS'/CMF's haven't thought about things like these before.

Not to boost your ego too much, but so far it's like you're reading my mind before I've even thought of the next problem. It's almost creepy :-\ ;D

[ryan]

Thanks Pete, that's very kind of you. This is still a pretty new project so there's plenty we haven't thought of yet too. We are all full time web designers and developers here, and I think we get a lot of good collaboration and thinking going on in these forums.

[Pete]

No worries.

Just thought of an amazing use of the page fieldtype - just for reference the site I'm tempted to convert to PW at some point is http://www.strategycore.co.uk - pretty much everything there aside from the Forums and Files sections is run on MODx with a bunch of custom snippets and templates - specifically the Articles and Databank parts of the site which at a quick glance at I'm sure you can see how simple it would be to do the same sort of structure in ProcessWire (took me a LOT of coding of snippets in MODx to get everything playing nicely together).

Since a lot of the pages reference each other, this is ideal - behind the scenes is a section for Companies, and for each game it references companies for Developer, Producer etc, and similarly in the Articles section each article has a field for which game it's associated with and so on.

I'm getting a bit excited about the possibilities, but lack of free time and other commitments mean it'll be a while before I realistically get time to look at setting up a PW installation to move all of that content across. Hopefully I'll get the chance to get stuck with it on a commercial project first - probably wiser if I start with something less complicated anyway to get my bearings!

[ryan]

Pete, that sounds like a cool site to develop in PW. I've always heard good things about MODx, though have never developed a site in it. It would be good to hear about the differences if you pursue this project sometime in the future.

[Adam Kiss]

I've always heard good things about MODx

^ that. It also always shattered, when I installed it.

Anyway, Pete: the strategycore is actually pretty much ideal thing you'd do in PW; you just create datasets, and then, with simple PageField (either single or multi, depending on the data) check whatever you want; The only 'hard' part is data import, because you'll need to do that transfer by yourself. I would actually advise you to start with this page – an hour a day maybe, lay down the data structure, fill in the data... and the templates will be a breeze.

[ryan]

While the data import may be the hardest part of this, it's probably also going to be a lot simpler than you think. If you can get the data you need to import into some standard format (like CSV, JSON, XML, etc.) then you'll have no problem getting it into PW. Here's a really simple example of importing a CSV that has has fields for 'title' and 'body':

<?php

$fp = fopen("data.csv", "r");
$parent = $pages->get("/path/to/parent/"); 
$template = $templates->get("some-template"); 

while(($data = fgetcsv($fp)) !== false) {

    $page = new Page();
    $page->parent = $parent; 
    $page->template = $template; 
    $page->title = $data[0];
    $page->name = $data[0]; 
    $page->body = $data[1];  
    $page->save();

}

When the time comes, just post some details about what you are doing and we can get you started.

[Adam Kiss]

I'm not saying it's hard as in tough to come up with solution, I mean it's tedious and might be complicated.

[ryan]

Translating data from one source to another can always be tedious and complicated. But at least the PW side of it is relatively easy. More advanced importers have to create and populate page references or handle mirroring of existing data in addition to importing of new data. I've built more of these than I can count, so glad to post examples or pass along any info I can anytime somebody needs to do something similar.

[Adam Kiss]

Hey all! We started to write quite a bit about games, so I split the topic and moved the gamers part into Pub: Gamers in PW community 1, here: http://processwire.com/talk/index.php/topic,353.0.html

[apeisa]

For the autocomplete – I'm going to finish the new ProcessSearch module first, because that will provide the JS ajax results for the autocomplete. Luckily, that part is almost done.

My co-worker is getting hands dirty with pw module development. He is planning to create Ajax-inputfield first. I don't remember if search can return json yet? If not, do you think that is big change to make?

[ryan]

I've just added it to ProcessPageSearch and committed the update to the P21 source. Now you can do something like this:

var url = config.urls.root + 'page/search/for?template=basic-page&body*=something'; 

$.getJSON(url, function(data) {
    console.log(data); 
}); 

The example above shows "search/for?" as the URL. When you add the "for" to it, it assumes that you are going to specify a selector in the URL. This works with either Ajax or regular mode. In ajax mode, it returns JSON. In regular (non ajax) mode it's going to output the results in interactive/html mode as usual. This "selector mode" isn't used in the search engine by default, so it's only used when you include the "for?" in your URL and specify a selector after it. I believe this selector mode is much more convenient than the regular mode for AJAX use, as well as regular non-ajax links to the search engine. 

The selector can be identical in format to a regular selector with the only difference being that you separate each part of the selector with a "&" rather than an ",". Here is an example:

/processwire/page/search/for?template=villa&body*=luxury&bedrooms>5&bathrooms<=3

The above is a valid URL, but it's also a valid selector. If we replace the "&" with ", ", then we get this:

template=villa, body*=luxury, bedrooms>5, bathrooms<=3

PW abstracts away the parsing of GET vars that lack an equals sign, like "bedrooms>5", and translates all of PW's 2-character operators just fine too (like *=, ~=, %=, <=, >=).

Of course, you can also do this (specify a parent path):

/processwire/page/search/for?parent=/path/to/parent&featured=1

If you want to specify what fields should appear in the JSON output (or the interactive table), then just add a "get=" var to your URL, like this:

/processwire/page/search/for?template=basic-page&get=title,path,categories

Separate out the fields you want to "get" with commas, like above.

When you specify what fields should be included, it will include them in the output, in addition to the native fields that are included in all (like id, name, template, parent, etc.).

Because JSON can represents objects nicely, PW will also include partial objects (like page references) as JSON objects. For something like a Page reference, it'll include most of the native fields for each page, as well as the title and path.

By default it will paginate by 50, though you can increase that up to 250 by specifying your own "limit=123" var in the URL (where 123 is the limit you want to specify). To retrieve the next pagination, specify the same limit but precede your URL with the page number, i.e.

/processwire/page/search/for/page2?template=basic-page&limit=100

If you prefer, you can specify a "start=123" var in your selector (just like with other PW selectors), to indicate the result number you want to start from. But I'm guessing most will prefer the simplicity of using the page number pagination.

If you call jQuery's getJSON() on the search URL, you will get your results in JSON format. Here's an example of a call we might use:

$.getJSON("/processwire/page/search/for?template=basic-page", function(data) {
    console.log(data); 
}); 

jQuery's getJSON automatically converts it to an object in jQuery, but the JSON string is probably the simplest way to envision it, so here is the actual JSON string as it's returned from that query. This query was performed on a stock install of PW 2.1 using the included profile.

<?php // ignore the PHP tag, I just wanted syntax highlighting 

{
    "selector":"template=basic-page, limit=50", // just a repeat of the selector you used
    "total":"4", // total number of results found. if there were 1230 matches, this would be 1230
    "limit":50, //  the max number of results that will be in this request (default=50)
    "start":0,
    "matches":[ // array of matches
        {
            "id":1001,
            "parent_id":1,
            "template":"basic-page",
            "path":"/about/",
            "name":"about",
            "title":"About"
            // plus any fields you specify with get=... 
        },
        {
            "id":1002,
            "parent_id":1001,
            "template":"basic-page",
            "path":"/about/what/",
            "name":"what", 
            "title":"Child page example 1"
        },
        // and so on for each match        
    ]
}

I'm sure there are tweaks and improvements still to be made, so please let me know if you find any issues or areas for improvement.

[apeisa]

Ryan, this is great! Thanks for your reply and the commit. Hopely Seba will register into forums soon enough. If not, then I will post his code here and take all the respect from his work

[Tom.]

I've just added it to ProcessPageSearch and committed the update to the P21 source. Now you can do something like this:

var url = config.urls.root + 'page/search/for?template=basic-page&body*=something'; 

$.getJSON(url, function(data) {
    console.log(data); 
});

The example above shows "search/for?" as the URL. When you add the "for" to it, it assumes that you are going to specify a selector in the URL. This works with either Ajax or regular mode. In ajax mode, it returns JSON. In regular (non ajax) mode it's going to output the results in interactive/html mode as usual. This "selector mode" isn't used in the search engine by default, so it's only used when you include the "for?" in your URL and specify a selector after it. I believe this selector mode is much more convenient than the regular mode for AJAX use, as well as regular non-ajax links to the search engine. 

The selector can be identical in format to a regular selector with the only difference being that you separate each part of the selector with a "&" rather than an ",". Here is an example:

/processwire/page/search/for?template=villa&body*=luxury&bedrooms>5&bathrooms<=3

The above is a valid URL, but it's also a valid selector. If we replace the "&" with ", ", then we get this:

template=villa, body*=luxury, bedrooms>5, bathrooms<=3

PW abstracts away the parsing of GET vars that lack an equals sign, like "bedrooms>5", and translates all of PW's 2-character operators just fine too (like *=, ~=, %=, <=, >=).

Of course, you can also do this (specify a parent path):

/processwire/page/search/for?parent=/path/to/parent&featured=1

If you want to specify what fields should appear in the JSON output (or the interactive table), then just add a "get=" var to your URL, like this:

/processwire/page/search/for?template=basic-page&get=title,path,categories

Separate out the fields you want to "get" with commas, like above.

When you specify what fields should be included, it will include them in the output, in addition to the native fields that are included in all (like id, name, template, parent, etc.).

Because JSON can represents objects nicely, PW will also include partial objects (like page references) as JSON objects. For something like a Page reference, it'll include most of the native fields for each page, as well as the title and path.

By default it will paginate by 50, though you can increase that up to 250 by specifying your own "limit=123" var in the URL (where 123 is the limit you want to specify). To retrieve the next pagination, specify the same limit but precede your URL with the page number, i.e.

/processwire/page/search/for/page2?template=basic-page&limit=100

If you prefer, you can specify a "start=123" var in your selector (just like with other PW selectors), to indicate the result number you want to start from. But I'm guessing most will prefer the simplicity of using the page number pagination.

If you call jQuery's getJSON() on the search URL, you will get your results in JSON format. Here's an example of a call we might use:

$.getJSON("/processwire/page/search/for?template=basic-page", function(data) {
    console.log(data); 
});

jQuery's getJSON automatically converts it to an object in jQuery, but the JSON string is probably the simplest way to envision it, so here is the actual JSON string as it's returned from that query. This query was performed on a stock install of PW 2.1 using the included profile.

<?php // ignore the PHP tag, I just wanted syntax highlighting 

{
    "selector":"template=basic-page, limit=50", // just a repeat of the selector you used
    "total":"4", // total number of results found. if there were 1230 matches, this would be 1230
    "limit":50, //  the max number of results that will be in this request (default=50)
    "start":0,
    "matches":[ // array of matches
        {
            "id":1001,
            "parent_id":1,
            "template":"basic-page",
            "path":"/about/",
            "name":"about",
            "title":"About"
            // plus any fields you specify with get=... 
        },
        {
            "id":1002,
            "parent_id":1001,
            "template":"basic-page",
            "path":"/about/what/",
            "name":"what", 
            "title":"Child page example 1"
        },
        // and so on for each match        
    ]
}

I'm sure there are tweaks and improvements still to be made, so please let me know if you find any issues or areas for improvement.

Stumbled pass this completely by accident, though I could see plenty of uses for this in previous projects I've done. I think for 3.0 the Documentation could do with a major refresh. 

It would be great to put maybe a team together to work on this. I'll be happy to chip in.