Search for related items

[Westy]

With a postgres database that has three related tables:

companies.company_id to contacts.company_id
contacts.contact_id to items.contact_id

How can I search for all the items for one company? I would prefer not to add a company_id column to the items table.

Dean

[ROCLASI]

Hi Dean,

You can use the Servoy approach and that means creating relationships back from items all the way to companies and search over them.
Or you use SQL and load that into the items form like so:

Code: Select all

var _sSQL = "SELECT items_id \
            FROM items \
            JOIN contacts ON (items.contact_id=contacts.contact_id) \
            JOIN companies ON (contacts.company_id=companies.company_id) \
            WHERE companies.name = ?";

controller.loadRecords(_sSQL, ['company x']);

Or when you select them by company_id you don't even need the join to companies because the contacts table already has the companies Foreign Key that you can use.

Hope this helps.

[Westy]

That was a very quick response. Thank you very much.

Dean

[Westy]

How would that look with the Servoy Find/Search approach? I am trying to visualize the proper syntax of the relationships back from items and how it should be placed within the Find/Search.

Dean

[ROCLASI]

Hi Dean,

Right now you have relationships from companies to contacts to items. This is all fine for searching on items to find a company or a contact but to get all items of companies you need the relationships from items to contacts to companies, so reversed. So for this to work you need to add the following relationships:
items_to_contacts: items.contact_id to contacts.contact_id
contacts_to_companies: contacts.company_id to companies.company_id

Then you can do a search on the items form like so:

Code: Select all

// search on company name
if ( controller.find() ) {
    items_to_contacts.contacts_to_companies.name = 'company x';
    controller.search();
}

// or when searching only for company_id you only need items_to_contacts like so
if ( controller.find() ) {
    items_to_contacts.company_id = 1;
    controller.search();

}

Another approach could be to to use the following code:

Code: Select all

// NOTE: first make sure you only have the company in your foundset you want the items from.

// get all contacts
var _fs = databaseManager.convertFoundSet(forms.companies.foundset, companies_to_contacts);

// get all items from these contacts
_fs = databaseManager.convertFoundSet(_fs,contacts_to_items);

// load the items in the form
forms.items.controller.loadRecords(_fs);

But this approach is much less efficient since it requires multiple queries.

Hope this helps.

[Westy]

When I do

Code: Select all

if ( controller.find() ) {
    items_to_contacts.company_id = 1;
    controller.search();
}

it works perfectly in a sample solution and database I created. Thank you for helping me understand the need to reverse the relation. My real database has the company and contacts tables in a contacts database and the items table in a items database. In that solution I created the same relation under the items form, however, when I try to perform the same Find a message "Relation items_to_contacts cannot be used in a find" appears. Any thoughts on what I might be doing wrong?

Dean
Servoy Version: 6.0.8 - build 1237

[ROCLASI]

Hi Dean,

Servoy supports relationships over different database connections to load related data but (sadly) doesn't support searching over them. That is what that warning is all about.
So if you want to load all the items of a group of contacts (in this case of a single company) you need to fetch the PK's of these contacts first and then use that result to search in the items table using a separate search. Sadly you can't just load the PK's in the foundset of the items table since these are contact PK's and not Items PK's so you need to use SQL to solve this.
So your code could look like this:

Code: Select all

// running on the contacts form (no real need to do this from companies)
var _ds,
    _sSQL = "SELECT item_id FROM items WHERE contact_id IN ({$ARRAY})";

if (controller.find()) {

    company_id = 1;
    controller.search();

    // get the contact PK's
    _ds = databaseManager.convertToDataSet(foundset, ['contact_id']);

    // replace the placeholder with the values in the array (turned back into a string joined by a comma)
    _sSQL = utils.stringReplace(_sSQL, '{$ARRAY}', _ds.getColumnAsArray(1).join(","));

    forms.items.controller.loadRecords(_sSQL);
}

If you prefer to use prepared statements instead of putting the PK's straight into the SQL then you could use the following code:

Code: Select all

var _ds,
    _sSQL = "SELECT item_id FROM items WHERE contact_id IN ({$ARRAY})",
    _aPlaceholders;

if (controller.find()) {

    company_id = 1;
    controller.search();

    // get the contact PK's
    _ds = databaseManager.convertToDataSet(foundset, ['contact_id']);

    // get an array with the correct amount of SQL placeholders (the questionmark) for each value in the array
    _aPlaceholders = _ds.getColumnAsArray(1).map(function() { return '?'; });

    // replace the {$ARRAY} placeholder with the string of SQL placeholders (i.e. ?,?,?,?,?, etc)
    _sSQL = utils.stringReplace(_sSQL, '{$ARRAY}', _aPlaceholders.join(','));

    // load the PK's with a prepared statement
    forms.items.controller.loadRecords(_sSQL, _ds.getColumnAsArray(1));

}

And a more advanced option would be to use a database link (SQL/MED) in the first database that maps the items table into that database so you can treat it as a regular table inside this database. But that is a database specific DBA task and not all databases vendors support this option (but Sybase, SQL Server, Oracle, DB2 and PostgreSQL do).

Hope this helps.

[Westy]

It works! I am using your last "prepared statements" example. This was a wonderful learning experience for me and will be of great benefit to my customers. Thank you very much for going the extra mile to help. I am humbled by the depth of your knowledge. I also notice that this was your 4807th post. Again, thank you.
Dean

[ROCLASI]

Hi Dean,

You are totally welcome.

As for it being a learning experience it goes both ways you know.

learning_piramid.jpg (43.89 KiB) Viewed 5583 times

And as with a lot of things there are multiple ways of reaching your goals and some of them are less efficient and some are just preferred coding styles. I just thought I give you a few options

As for options, I am not sure but you could try to use the databaseManager.convertFoundSet() approach I mentioned in my second post of this thread. It might even work with relations over 2 different db connections but I haven't tested this myself.

[Westy]

The databaseManager.convertFoundSet() did not work, maybe because I am using the two data connections, however, the last option does work.

>I just thought I give you a few options<
Options and choices are good (that's why the name of my company is Choices). Your options really do help me to better understand and are very much appreciated.

I like that chart. It is very true.

Dean