Custom Database

[scottc]

Hi Everyone, I am new to processwire and have been reading through the forums and docs for some time now. I have an older version that I am working on for a client ( 2.7.3 ). I have a database that I just created through SQLPro that ports in data via a cron job every morning. Within the site, there are search pages and I need to sort by data within this database. How do I attach it to the $page ( maybe im saying this wrong ) so that I can sort by not only existing fields but data within this table as well.

data is av_date, av_status

so say the search was for a listing of properties that are located in a certain area ( field_location ) that are a certain price ( field_rate ) but now I need to know if they are listed in the custom table and if so what their av_date(s) are depending on av_status.

I tried taking an existing module and repurposing it but that did not work. Any help would be appreciated at this point.

[dragan]

Why don't you create those fields av_date and av_status in your PW templates, and then populate / update these fields inside PW with your cron job? This would make it easier to query such things.

[scottc]

Hi Dragan, thanks for the reply. I could do that but each av_date has a specific av_status so individual fields would not link up right, unless this is possible?

[dragan]

I'm afraid I don't understand the whole scenario/setup. It would help if you'd explain a bit more in detail what these DB fields actually contain and how they relate to PW-content. I'm sure we'll find a way to solve this, but right now, the issue (for me) is a bit foggy...

Oh, and btw, welcome to the forum ?

 

[scottc]

Thank you Dragan, so i did some more digging and realized that with a little manipulation through a function all i need to store is the av_date. There many of these per page. Right now there are 130k of them between 1000 pages. I created a function to select the data from my cron table and insert it into the new field table ( field_availability ) but got this error:

Duplicate entry '12503' for key 'PRIMARY'

I need pages_id, the default in fields, to not be unique. If I change this will it disrupt the ability to search?

 

[dragan]

As I mentioned before, the whole setup is not clear to me.

It seems as if you have a one-to-many situation here, i.e. you have several av_dates that point (relate) to one "parent" (page). That's all perfectly doable, but if you don't explain your whole setup any better, we're all just guessing here and stabbing in the dark. The first step to getting a constructive + helpful answer is to phrase your problem as best as you can.

3 hours ago, scottc said:

I created a function to select the data from my cron table and insert it into the new field table ( field_availability ) but got this error:

This seems to suggest you are still saving data in extra DB tables - whereas I was suggesting to save that right inside your related PW pages instead.

[bernhard]

16 minutes ago, dragan said:

As I mentioned before, the whole setup is not clear to me.

Same here.

Welcome to the forum @scottc , please explain everything more and give some examples and explain what all those fields are for (av_date might have a meaning for you but it does not have one for me ? ). You should also explain what kind of data you have in your database and what kind of data you have in your PW installation. And last but not least: Why you have it there (eg you get data in the database format from a 3rd party system that you cannot change or you need to store this and that inside PW because there is a frontend search for clients etc...).

Give us the bigger picture and we can suggest you some ways you could go. I'm quite sure there will be many ways - as with PW most of the time you have free choice ?

[scottc]

Hi guys, sorry i have been working through this today. This is where I am:

I installed the module ProFields:table. Modified the module so the pages_id ( and sort ) field did not default to unique index, just index. Created a column with av_date in it and I now have the dataset I was looking for. field_availabilities.

Now, im stuck on the search. So the user will be able to filter a search by location, bedrooms, special features and now date availability. The previous 3 were existing already and basically the function goes through abd builds a query ( selector ) and then runs it. I need to show the properties at the top that have availability. The challenge is that not every property is listed in the field_availabilities table. is there a way to say "sort by anaything that exists in this table on top"?