Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

Template Questions

Arraylist and queries


Reply

5 Posts   867 Views

Avatar
Drumstick

7 September 2013 at 7:46am Community Member, 20 Posts

Hi

Maybe this question is old but I didn't find an answer. I have 3 DB querys, one is for a product the other for picutres many_many relation. The result is ok, but now I push them in a Arraylist and it loops 4 records, but it should be just one record. When I make two Arraylists, one for the product, the other for the pictures, then I have the problem that I can't loop in a loop, but the record of the prodcut is one and that is correct. But I have to loop in a table, and there you can click on a result and then it popups a dialog box. So I have to loop my product result and then the images in the product loop. But this does not work. How can I do this?

Does somenone know? Or what do I wrong. The result of the db queries are correct, so there can't be a mistake in my opinion. The problem is in the merge of the several results and the loop in a loop.

Thanks a lot for help.

Avatar
Willr

7 September 2013 at 1:44pm Forum Moderator, 5511 Posts

then I have the problem that I can't loop in a loop

Why can't you loop inside a loop? So you images link to a product through a relation. Lets say your model looks like

class Product extends DataObject {

static $many_many = array(
'Images' => 'ProductImage'
);
}

And you get your list of products for the page like something

function getProducts() {
return Product::get();
}

To get your images in the template you would then have something like

<% loop Products %>
<% loop Images %>
$Filename
<% end_loop %>
<% end_loop %>

If you post a link to your code we can help in more detail.

Avatar
Drumstick

7 September 2013 at 5:57pm (Last edited: 7 September 2013 6:17pm), Community Member, 20 Posts

Hi Willr

Thanks a lot for answer.

I have a form in which you can choose your product, you can choose your place code and the distance...now I need as result the products from the places in this distance....and the products have pictures

For the products I have a drop down solution with jquery, so you don't see a map there because of that..but it works.

Here is my code:

public function ProductsearchForm() {

$fields = new FieldList(

DropdownField::create(
'Productcategory',
'Produktkategorie'
),

DropdownField::create(
'Productsubcategory',
'Subkategorie'
),
DropdownField::create(
'Product',
'Produkt'
),

TextField::create(
'PLZ',
'PLZ'
),
TextField::create(
'Place',
'Ort'
),
TextField::create(
'Distance',
'Umkreis km'
)

);

$validator = new RequiredFields(
'Productcategory',
'Distance'

);

$actions = new FieldList(FormAction::create("submit")->setTitle("Suchen"));

return new Form($this, 'ProductsearchForm', $fields, $actions,$validator);

}

public function submit(array $data, Form $form) {

$plz = $data['PLZ'];
$ort = $data['Place'];
$distance = $data['Distance'];
$product = $data['Product'];

$sqlQuery = new SQLQuery();
$sqlQuery->setFrom('Customerproduct');
$sqlQuery->addWhere("Product = '$product'");
$sqlQuery->addLeftJoin('Customer', '"Customerproduct"."CustomerID" = "Customer"."ID"');
$sqlQuery->addWhere('PLZ = '.$plz);

$rawSQL = $sqlQuery->sql();

// Execute and return a Query object
$result = $sqlQuery->execute();

$lng = "";
$lat = "";
$userid = array();
$productid = array();
// Iterate over results
foreach($result as $row) {
$lng = $row["Longitude"] / 180 * M_PI;
$lat = $row["Latitude"] / 180 * M_PI;
$userid[$row['ID']] = $row['ID'];

};

$sqlQuery = new SQLQuery();
$sqlQuery->addFrom('Customer');

$sqlQuery->selectField("(6367.41*SQRT(2*(1-cos(RADIANS(Customer.Latitude))*cos(".$lat.")*(sin(RADIANS(Customer.Longitude))*sin(".$lng.")+cos(RADIANS(Customer.Longitude))*cos(".$lng."))-sin(RADIANS(Customer.Latitude))* sin(".$lat."))))","Distanz");

$sqlQuery->addLeftJoin('Member','"Customer"."ID" = "Member"."ID"');
$sqlQuery->addWhere("Member.ID IN(".implode(",", $userid).")");
$sqlQuery->addLeftJoin('Customerproduct', '"Customer"."ID" = "Customerproduct"."CustomerID"');
$sqlQuery->addLeftJoin('Customerinfo','"Customer"."ID" = "Customerinfo"."HofinfophotoID"');

$sqlQuery->addWhere("(6367.41*SQRT(2*(1-cos(RADIANS(Customer.Latitude))*cos(".$lat.")*(sin(RADIANS(Customer.Longitude))*sin(".$lng.")+cos(RADIANS(Customer.Longitude))*cos(".$lng."))-sin(RADIANS(Customer.Latitude))*sin(".$lat."))) <= '".$distance."')");

$sqlQuery->setOrderBy('Distanz','ASC');

// Get the raw SQL (optional)
$rawSQL = $sqlQuery->sql();

// Execute and return a Query object
$result = $sqlQuery->execute();

$item = new ArrayList();

// Iterate over results
foreach($result as $row) {

$output =new ArrayData(array(
'Hof' => $row
));
$item->push($output);
}

$sqlQuery = new SQLQuery();
$sqlQuery->addFrom('Customerproduct_Productphoto');
$sqlQuery->addLeftJoin('Customerproduct','"Customerproduct_Productphoto"."CustomerproductID" = "Customerproduct"."ID"');
$sqlQuery->addWhere("Customerproduct.CustomerID IN(".implode(",", $userid).")");

$rawSQL = $sqlQuery->sql();

// Execute and return a Query object
$result = $sqlQuery->execute();

$fotoid = array();
// Iterate over results
foreach($result as $row) {

$fotoid[$row['ImageID']] = $row['ImageID'];

};

$sqlQuery = new SQLQuery();
$sqlQuery->addFrom('File');

$sqlQuery->addWhere("ID IN(".implode(",", $fotoid).")");

$rawSQL = $sqlQuery->sql();

// Execute and return a Query object
$result = $sqlQuery->execute();

$itembilder = new ArrayList();

// Iterate over results
foreach($result as $row) {

$output =new ArrayData(array(
'Productbilder' => $row
));
$itembilder->push($output);

};

$sqlQuery = new SQLQuery();
$sqlQuery->addFrom('File');
$sqlQuery->addLeftJoin('Customerinfo','"File"."ID" = "Customerinfo"."HofinfophotoID"');
$sqlQuery->addWhere("Customerinfo.CustomerID IN(".implode(",", $userid).")");

$rawSQL = $sqlQuery->sql();

// Execute and return a Query object
$result = $sqlQuery->execute();

// Iterate over results
foreach($result as $row) {

$output =new ArrayData(array(
'Hofbild' => $row
));
$itembilder->push($output);

};

$hofdaten = array();
$hofdaten['Hofdaten'] = $item;
$bilder = array();
$bilder['Bilder'] = $itembilder;

$custom = array_merge($hofdaten,$bilder);

$count = count($item);

// Debug::show($custom);

if($count > 0) {
$this->setMessage('Success', 'Es konnten '.$count.' Treffer gefunden werden');

} else {
$this->setMessage('Error', 'Es konnten keine Treffer gefunden werden');
}

return $this->customise($custom);

}

my template looks like this, but the loop of Bilder does not work...:

<% if Hofdaten %>
<table class="searchtable">
<tr><th>Hofname</th><th>Adresse</th></tr>

<% loop Hofdaten %>

<tr height="5" bgcolor="#e9e9e9"><td colspan="2"></td></tr>
<tr bgcolor="#e9e9e9">

<td><a data-toggle="modal" href="#suchresultat$Pos" class="">-> $Hof.Hofname</a>

</td>
<td>
$Hof.Surname $Hof.FirstName<br />
$Hof.Street<br />
$Hof.PLZ $Hof.Place
</td>
</tr>
<tr height="1" bgcolor="#ccc"><td colspan="2"></td></tr>
<tr height="5" bgcolor="#e9e9e9"><td colspan="2">
<!-- Modal -->
<div class="modal hide fade" id="suchresultat$Pos">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-hidden="true">&times;</button>
<img src="$ThemeDir/images/logo_hofladen_klein.png" alt="Hofladen" /><br /><br />

</div>
<div class="modal-body">

<h4>$Hof.Hofname</h4>

<% loop Bilder %>
<img src="$Hofbild.Filename" />
<% end_loop %>

<p>$Hof.Surname $Hof.FirstName</p>
<p>$Hof.PLZ $Hof.Street</p>
<p>Telefon: $Hof.Phone</p>
<p>Email: $Hof.Email</p>
<p>Internet: <a href="$Hof.Homepage">$Hof.Homepage</a></p>
<br />
<br />
<h5>Aus unserem Sortiment</h5>
<p>
$Hof.Productcategory, $Hof.Productsubcategory, $Hof.Product, $Hof.Price / $Hof.Einheit<br />
$Hof.Description
<img src="$Productbilder.Filename" />
</p>

</div>

</div><!-- /.modal-content -->
</div><!-- /.modal-dialog -->
</div><!-- /.modal -->

</td></tr>
<% end_loop %>

</table>

<% end_if %>

Avatar
Drumstick

8 September 2013 at 8:29am Community Member, 20 Posts

Hi Willr

I think I just see the solution to loop in the table the name of the Hof and the adress and then a link added with the user id. Then go to a detail page and show the result by query the database with the userid. Otherwise I don't understand how to bring together all this things like it is now.

Avatar
Willr

8 September 2013 at 11:41am Forum Moderator, 5511 Posts

The loop of Bilder doesn't work as it's not added to your returned DataObjectSet. If you want to loop Bilder within the loop of Hofdaten then the Bilder data has to be on each Hofdaten object.

E.g based on your template you want to be passing the data structure something like

function getHofdaten() {
// ...
foreach($result as $row) {
//
$bilder = new DataObjectSet();
$bilder->push( .. );

$output->push(new ArrayData(array(
'Hof' => new ArrayData($hof),
'Bilder => $bilder
Productbilder' => something
}

return $output;
}

Remember when you use <% loop ObjName %> you are now inside the scope of ObjName and can only access properties in ObjName (or us $Up to go outside the loop) so depending on your data model you'll want to put everything into the correct scope.