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

Joel Shapiro jsfmp at earthlink.net
Mon Mar 20 15:15:56 MST 2006


Thanks very much Andy, that all makes good sense.

Question for you on your example, though.  You state at the end of  
your post the possibility of displaying related contact- 
phonenumbers.  If I understand correctly, this would be a  
'grandchild' relationship: Business->Contact->ContactPhone.  How  
would this be possible using only portals -- and not additional  
queries?  (i.e. wouldn't you need to have a portal within a portal?   
[& not using GetNthRecord calcs])

Thanks,
-Joel


On Mar 20, 2006, at 1:51 PM, Andy Gaunt wrote:

> 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
>
>
> _______________________________________________
> FX.php_List mailing list
> FX.php_List at mail.iviking.org
> http://www.iviking.org/mailman/listinfo/fx.php_list



More information about the FX.php_List mailing list