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

[Joel Shapiro]

Hi Derrick

So are you able to display a portal within a portal?  Or are you  
using a GetNthRecord calc field in the Child records to show the  
Grandchildren records within the Child portal?  Or are you doing  
something else magic??

-Joel


On Mar 21, 2006, at 6:13 AM, Derrick Fogle wrote:

> If you can display it in a portal in FM, you can get that exact  
> same data into FX.php. So it depends on the FM relationships. I  
> have at least one instance where I display almost exactly what you  
> describe below
>
> On Mar 20, 2006, at 4:15 PM, Joel Shapiro wrote:
>
>> 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
>>
>> _______________________________________________
>> FX.php_List mailing list
>> FX.php_List at mail.iviking.org
>> http://www.iviking.org/mailman/listinfo/fx.php_list
>
>
> Derrick Fogle
> derrick at fogles.net
>
>
>
> _______________________________________________
> FX.php_List mailing list
> FX.php_List at mail.iviking.org
> http://www.iviking.org/mailman/listinfo/fx.php_list