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.

We've moved the forum!

Please use forum.silverstripe.org for any new questions (announcement).
The forum archive will stick around, but will be read only.

You can also use our Slack channel or StackOverflow to ask for help.
Check out our community overview for more options to contribute.

Template Questions /

Moderators: martimiz, Sean, Ed, biapar, Willr, Ingo, swaiba

Arraylist and queries


Go to End


5 Posts   3243 Views

Avatar
Drumstick

Community Member, 20 Posts

7 September 2013 at 7:46am

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

Forum Moderator, 5523 Posts

7 September 2013 at 1:44pm

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

Community Member, 20 Posts

7 September 2013 at 5:57pm

Edited: 07/09/2013 6:17pm

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

Community Member, 20 Posts

8 September 2013 at 8:29am

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

Forum Moderator, 5523 Posts

8 September 2013 at 11:41am

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.