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

[Joel Shapiro]

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