[FX.php List] Portal vs 2nd query [Was: Related Records]

[Andy Gaunt]

Joel,

I personally find portals on the layout to be faster. The reason being, it
is a single query to FileMaker.

It can also depend on what you are doing. Say your first query is for
companies you have done business with in the last 30 days. This returns 50
records.

Now, you are looping through those company records with your FX.PHP to find
the Contacts at that company. So, you do a second FX.PHP query.

This then returns 5 records for company 1, 10 for company 2, 3 for company 3
and so on.

The issue here is that you are doing your second FX query within a foreach
loop of the initial query. So instead of 1 query to the database we now have
51 queries.

1 for the initial search and then 50 for the related contacts.

Imagine if you also wanted to display contact numbers for those contacts
that is stored in another table. Add this to the mix and you start to see
the problem.

If you use portals, you can limit your number of queries and use simple for
and while loops to process this data. Plus, you will only loop through the
FX.PHP data array once this way, as opposed to multiple times.

My 2cents anyways.


Andy Gaunt
T: 407.810.4722
andy at fmpug.com
http://www.fmpug.com    

Recipient of FileMaker's 2005 "Mad Dog" Public Relations Award

For chapter locations, dates & times please visit the website at
http://www.fmpug.com If you can make it to a meeting, please RSVP at
http://www.fmpug.com/rsvp.php


-----Original Message-----
From: fx.php_list-bounces at mail.iviking.org
[mailto:fx.php_list-bounces at mail.iviking.org] On Behalf Of Joel Shapiro
Sent: Monday, March 20, 2006 3:36 PM
To: FX.php Discussion List
Subject: [FX.php List] Portal vs 2nd query [Was: Related Records]

Hi all

I've often wondered which produces better performance (or is better  
design):

a) Having a relationship in FileMaker and viewing related records  
through the portal (and code similar to Derrick's below),
   or
b) Keeping the FM tables as 'raw data' and querying the related  
records through PHP after first querying the 'parent' record.

I have one page in a project with multiple 'nested' queries, since  
you can't have portals within portals, but when it's just a matter of  
multiple children to one parent, how does the 2nd query balance  
against the FM relationship - performance/stability-wise?

Any thoughts or words of wisdom?

TIA,
-Joel


Joel Shapiro - FileMaker Pro Database Design
~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
joelshapiro at jsfmp dot com
http://www.jsfmp.com
415-269-5055


On Mar 20, 2006, at 8:28 AM, Derrick Fogle wrote:

> If you want it to be similar to an FM portal, then use the portal.  
> The last array element in any FX field reference indicates it's  
> repeating field number or portal row number. You only need a single  
> database query, and will get all the data on the referenced layout.  
> Here's a sample of the code I use:
>
> $prow = 0;
> while($value['Relationshipname::ID_Field'][$prow]) {
> 	echo $value['Relationshipname::Data_Field'][$prow];
> 	echo "<br />";
> 	$prow++;
> }
>
> Salt to taste...
_______________________________________________
FX.php_List mailing list
FX.php_List at mail.iviking.org
http://www.iviking.org/mailman/listinfo/fx.php_list