FAQ Database Discussion Community


CakePHP 3 build complex join using query builder

cakephp,query-builder,cakephp-3.0
I need to add joins on the same tables dynamically... something like this: // foreach ... as $id $options['join']['T_Values' . $id] = [ 'type' => 'LEFT', 'table' => 't_values', 'conditions' => ['T_Values' . $id . '.t_id = T.id'] ]; $options['join']['Values' . $id] = [ 'type' => 'LEFT', 'table' => 'values',...

Many to Many Query Buider Symfony2

symfony2,orm,many-to-many,query-builder
I have two entities. Category and Product. They mapped ManyToMany. class Category { ... /** * @var string * * @ORM\Column(name="name", type="string", length=255) */ private $name; ... } and Product class class Product { ... /** * @var string * * @ORM\Column(name="name", type="string", length=255) */ private $name; /** * *...

Laravel 5 complex AND WHERE query not working as expected

php,laravel,query-builder
I have a issue with the following code: $clients_counter = 0; $cost = 0; $query = DB::table('clientes')->where('recibe_sms', '=', '1') ->where(function($q) { $q->orWhere('movil_1', '<>', '') ->orWhere('movil_2', '<>', '') ->orWhere('otro_movil', '<>', ''); }); $with_moto = (Input::has('moto')) ? 1 : 0; $with_coche = (Input::has('coche')) ? 1 : 0; $with_camion = (Input::has('camion')) ? 1...

Laravel: whereIn does not use given array as argument

laravel,query-builder
Here are two lines of code which looks quite the same. 1: $q = \App\User::whereIn('id',[1,2,3]); 2: $users = DB::table('users')->whereIn('id', array(1, 2, 3)); But its result are different. The line 1 does not select any result. Its collection is empty. The line 2 could select correct users where its user id...

Limiting Bucket Search using QueryBuilder field type in Sitecore

sitecore,query-builder,buckets,sitecore8
In our Sitecore application, we have a template called NewsArticleSlide which renders a news article on a carousel slide. This template has a field called "News article" which contains the ID of the news article being rendered. The type of this field up to now has been droplink, which has...

What is wrong in the Query Builder or in the call itself?

php,symfony2,doctrine2,query-builder
I'm have this function in a repository: public function buscarConstanciaProducto($solicitudUsuario, $productoSolicitud) { $builder = $this->getEntityManager()->createQueryBuilder(); $builder->select(array('su', 'p', 'ps', 'c')) ->from('AppBundle:SolicitudUsuario', 'su') ->leftJoin('su.producto_solicitud', 'ps') ->leftJoin('ps.producto', 'p') ->leftJoin('su.constancias', 'c') ->where('su.id = ?', $solicitudUsuario) ->andWhere('ps.id = ?', $productoSolicitud); return $builder->getResult(); } I'm trying to...

Laravel: How does the query builder handle ->first()?

php,laravel,query-builder
How does the query builder handle ->first()? Does it simply do a Limit 1 or does it do some advanced checking to make sure if only 1 row is returned? In rare cases, the application may want to ensure that ONLY 1 row will be returned. Perhaps ->first() is not...

Getting fresh result set using Laravel's Query Builder

laravel,laravel-4,query-builder
When I create this query: $data = DB::table('data') ->leftJoin('urls', 'data.url_id', '=', 'urls.id') ->where('urls.tag', $tag) ->where('urls.requester_id', $requester_id) ->orderBy('data.created_at') ->take(100); And then get the first row to perform some validations on it: $sample = $data->first(); Any subsequent calls will only return that first row, even if I use get(): $all_rows = $data->get();...

Laravel - named subquery using Query Builder

php,mysql,laravel,subquery,query-builder
In MySQL subqueries documentation there's an exmaple of subquery: SELECT ... FROM (subquery) [AS] name ... Here's the raw query which I want to transform: select SUBQUERY_NAME.* from (select id, name from items) AS SUBQUERY_NAME Is there any way to do this in Laravel Query Builder without using DB::raw()?...

Laravel: how to use derived tables / subqueries in the laravel query builder

php,laravel,subquery,query-builder
Edit: Though this question originally was specific for the query I'm describing underneath, the answer I got applies to almost all questions related to using derived tables / subqueries in Laravel Original Question: Lately I'm a bit stuck on the laravel query builder. It has some really nice features but...

How to search a cassandra collection map using QueryBuilder

cassandra,query-builder,cql3,datastax-java-driver
In my cassandra table i have a collection of Map also i have indexed the map keys. CREATE TABLE IF NOT EXISTS test.collection_test( name text, year text, attributeMap map<text,text>, PRIMARY KEY ((name, year)) ); CREATE INDEX ON collection_test (attributeMap); The QueryBuilder syntax is as below: select().all().from("test", "collection_test") .where(eq("name", name)).and(eq("year", year));...

SQL-Unable to display count value that contain 0

mysql,sql,sql-server,inner-join,query-builder
There is 2 Table Discussion and Comment, Discussion tablehas D1 and D2 Comment table has (C1,D2),(C2, D2) SELECT Discussion.DiscussionID, COUNT(Comment.CommentID) AS Expr1 FROM Comment INNER JOIN Discussion ON Comment.DiscussionID = Discussion.DiscussionID INNER JOIN Category ON Discussion.CategoryID = Category.CategoryID GROUP BY Discussion.DiscussionID I want a output like this ... Dicussion ID||...

Laravel Querybuilder how to skip and take rows from newest to oldest?

php,mysql,laravel,query-builder
Is there a way to skip and take rows from newest to oldest using Laravel Querybuilder? This is what I currently have: DB::table('decks')->skip(10)->take(5)->get(); It skips from the oldest to the newest. Is there a way to make skip start from the newest rows?...

Laravel Query Builder - sum() method issue

php,mysql,database,laravel,query-builder
Ehi there, I'm new in laravel and I have some issues with laravel query builder. The query I would like to build is this one: SELECT SUM(transactions.amount) FROM transactions JOIN categories ON transactions.category_id == categories.id WHERE categories.kind == "1" I tried building this but isn't working and I can't figure...

With a OneToMany relation between entities, how create the right queryBuilder with Doctrine (on the Inversed Side entity)

php,symfony2,doctrine2,one-to-many,query-builder
I have these 3 entities in my symfony2.6 project: Compteurs.php class Compteurs { /** * @var \PointsComptage * * @ORM\ManyToOne(targetEntity="PointsComptage", inversedBy="compteurs") * @ORM\JoinColumns({ * @ORM\JoinColumn(name="pointscomptage_id", referencedColumnName="id") * }) */ private $pointsComptage; /** * @var \Doctrine\Common\Collections\Collection * * @ORM\OneToMany(targetEntity="ParametresMesure", mappedBy="compteurs") */ private $parametresMesure; ParametresMesure.php: class ParametresMesure { /** * @var Compteurs...

Kohana 3. Join with multiple condition using OR

join,kohana,query-builder,kohana-3.3
I'm using Kohana Query Builder and trying do next: $query ->join(«t1», «INNER») ->on(«t1.id»,»=»,»t2.parent_id») ->on(«t1.id»,»=»,»t3.x_id») This means: INNER JOIN t1 ON(t1.id = t2.parent_id AND t1.id = t3.x_id) But how force to use OR instead of AND of KO3 query builder join methods? INNER JOIN t1 ON(t1.id = t2.parent_id OR t1.id =...

laravel search multiple words separated by space

php,mysql,laravel,eloquent,query-builder
I am new to laravel query builder, I want to search multiple words entered in an input field for example if I type "jhon doe" I want to get any column that contains jhon or doe I have seen/tried solutions using php MySQL but can't able to adapt to query...

query builder gives exeption

symfony2,doctrine,query-builder
i am using query builder like $querym =$querym->select('DISTINCT(a.id)') ->where ('a.doctorid =:u') ->setParameter('u', $u); $query =$query->select('DISTINCT(d.id),d.name,d.dob,d.mobile') ->innerJoin('d.PatientSymptoms','ps') ->innerJoin('ps.symptoms', 's') ->andWhere('ps.symptoms =:name') ->where($query->expr()->In('d.id', $querym)) ->setParameter('name', $name); gives me Error: Method Doctrine\Common\Collections\ArrayCollection::__toString() must not throw an exception is it possible to write it some...

Convert mysql query logic to Laravel query builder

php,mysql,laravel,laravel-4,query-builder
I am trying to convert my mysql query logic to Laravel query builder. I I have no Idea how to convert it as laravel query. my query logic is SELECT id,name, case when visibility_status = '1' then 'Visible' when visibility_status = '0' then 'Invisible' end as visibility_status FROM `flowers` generally...

Doctrine2 QueryBuilder: How to filter out entities having zero count for a OneToMany

symfony2,doctrine2,associations,one-to-many,query-builder
In my Dymfony2 / Doctrine2 application, I have a oneToMany relation between an object and its children. I want to select all objects which have no children. I'm stuck with various errors: SingleValuedAssociationField expected, Cannot add Having sth on non result variable, etc. $queryBuilder = $this ->createQueryBuilder('object') ->leftJoin('object.children', 'children') ->andWhere('children...

How would I do this query in laravel

mysql,laravel,query-builder
How can I do this in laravel query builder? (this basically gets your current position in a voting system) SELECT position FROM (SELECT participant.target_user_id, @rownum := @rownum + 1 as position FROM (SELECT target_user_id, count(*) as votes FROM contest_participants_votes GROUP BY target_user_id ORDER BY votes DESC) as participant JOIN (SELECT...

Laravel query builder returns object or array?

php,laravel,query-builder
I'm building a very simple web app with Laravel. I've built two separate Controllers, which each return two separate views, as follows: ProfileController: class ProfileController extends BaseController { public function user($name) { $user = User::where('name', '=', $name); if ($user->count()) { $user = $user->first(); $workout = DB::table('workouts')->where('user_id', '=', $user->id)->get(); Return View::make('profile')...

Query Builder for DATE type

c#,date,query-builder
I'm having a problem with my query builder. I have a datagridview which I want to filter through. I want to allow search by "NAME, SURNAME, DATE". Here is my SQL QUERY SELECT exDate, exName, exSurname FROM examines WHERE (exDate = @Param1) AND (exName = @Param2) AND (exSurname = @Param3)...

How do I perform a join in Laravel 5 with a `where` clause?

php,mysql,laravel,laravel-5,query-builder
I have a table, offers, with purchases which has an id for the purchased item. The item is stored in another table, items. In response to a GET request, I want to return the full item object with purchases. How do I go about doing this? public function getBuys(Request $request)...

Laravel query builder - re-use query with amended where statement

php,mysql,laravel,query-builder
My application dynamically builds and runs complex queries to generate reports. In some instances I need to get multiple, somewhat arbitrary date ranges, with all other parameters the same. So my code builds the query with a bunch of joins, wheres, sorts, limits etc and then runs the query. What...

AEM Predicate - how to check if property starts with / contains string?

cq5,aem,query-builder
I'm using query builder to search for images in DAM. I use predicates to do that. I'm trying to check metadata dam:MIMEtype property, to return all nodes which starts from image/. How can I do that?...

How to give a name to the grouped items in sql with laravel db query?

php,mysql,laravel,query-builder
with this code i get this output: $q = DB::table('payments') ->join('agency', 'agency.id', '=', 'payments.agency') ->join('paymenttype','paypemttype.id', '=', 'payments.paymenttype') ->select(DB::raw('sum(payment) as sum'), 'agency.agency', 'paymenttype.paymenttype') ->where('payments.school', '=', Sentry::getUser()->school) ->groupBy('payments.agency') ->groupBy('payments.paymenttype') ->get(); Output is: [ { sum: 200, agency: "city1", paymenttype: "credit card" }, { sum: 200, agency: "city1", paymenttype:...

Set a row to 1 while others to 0 expressed in Eloquent

php,mysql,laravel-4,eloquent,query-builder
Cardholders can have but one primary credit card in mysql table. 1 if using for primary and other not in use value is 0. What I need is: UPDATE credit_cards SET primary=IF(id=cardholder_id, 1, 0) What I have so far is: $cardholder_id = Input::get('cardholder_id'); $card_id = Input::get('card_id'); // For updating that...

CodeIgniter “like()” function with % wildcard inside search terms

php,mysql,codeigniter,query-builder
Let's say I have function like this: public function get_list($category = '', $limit = 10, $offset = 0) { if (!empty($category)) $this->db->where('category', $category); $search = $this->input->get('search'); if (!empty($search)) $this->db->or_like(array('foo_column'=>$search)); $query = $this->db->get('table_name', $limit, $offset); //echo $this->db->last_query(); return $query->result(); } Produce query as : SELECT * FROM table_name WHERE foo_column LIKE...

Custom Column Names having spaces Laravel 4

php,mysql,laravel-4,query-builder
I want to use a custom column name that has spaces.How can I write the query for this using Laravel 4. I tried $org = Org::select('name as Organization Name')->where('id',$user->orgId); $org = Org::select('name as "Organization Name"')->where('id',$user->orgId); $org = Org::select('name as `Organization Name`')->where('id',$user->orgId); $org = Org::select('name as (Organization Name)')->where('id',$user->orgId); $org = Org::select('name...

How to define a Where query with 2 conditions, with QueryBuilder in Cassandra?

cassandra,bigdata,query-builder
I have this statement: SELECT * FROM users WHERE id='12' AND fname ='aaa'; How i use the same statement in QueryBuilder like: Statement statement = QueryBuilder.select().all().from("users").where(eq("id", 12))); ...

Combining Spring Data query builder with Spring Data JPA Specifications?

jpa,spring-data,specifications,query-builder
Spring Data allows you to declare methods like findByLastname() in your repository interface and it generates the queries from the method name automatically for you. Is it possible to somehow have these automatically-generated queries also accept a Specification, so that additional restrictions can be made on the data before it's...

Laravel Query Builder WHERE NOT IN

mysql,laravel,query-builder
I have the following sql query SELECT * FROM exams WHERE exams.id NOT IN (SELECT examId FROM testresults) how can I convert it into Laravel query builder format? Thanks....

Laravel 5: Convert query builder to eloquent

php,eloquent,laravel-5,query-builder,php-carbon
Is there a way to transform this query builder to eloquent? $users = DB::table('connections') ->join('locations','connections.to_user','=','locations.user_id') ->join('users','connections.to_user','=','users.id') ->select( DB::raw("*, IF(( 3959 * acos( cos(radians(".$lat.")) * cos(radians(latitude)) * cos(radians(longitude) - radians(".$lng.")) + sin(radians(".$lat.")) * sin(radians(latitude))) ) < ".$distance.",true,false) near,( 3959 * acos( cos(radians(".$lat.")) * cos(radians(latitude)) * cos(radians(longitude) -...

List box and Option group filtering 3 different rows in MAIN list box in MS Access 2013

ms-access,filter,ms-access-2013,query-builder,iif
I've been struggling with the following for a while and would be more than happy for some brainpower ;) I have an Advanced filter form, which filters through orders via many different filters, currently the one that I can't make function is the following: I have 4 controls on a...

Complex query 3 tables with 2 INNER JOIN, 1 subquery, 2 Group By in Laravel

mysql,laravel,laravel-4,eloquent,query-builder
This is my last attempt to create a complex query in Laravel. I have 3 tables I need for this scenario: photos, events, countries. Each event has many or none photos, each country may have multiple events with photos. My result shows PhotosByCountry, EventsByCountry, rContinent, rCountry. And here is the...

write query using Doctrine Query Builder

symfony2,doctrine2,query-builder
i have three table Patient,Symptoms, PatientSymptoms patient Symptoms PatientSymptoms id name id,name pid,sid i can write the query using sql SELECT Distinct(p.id) FROM Patient p join PatientSymptoms on p.id = PatientSymptoms.patient_id join Symptoms s on PatientSymptoms.symptom_id = symptoms.id; i tried using query builder $repo=$em->getRepository("EntityBundle:Patient"); $query = $repo->createQueryBuilder('d'); $query->leftjoin('d.patientSymptoms', 'r') ->where('r.symptoms...

CQ QueryBuilder orderby component order within a page

cq5,query-builder,aem,sling
I'm trying, in Adobe CQ 5.6.1, to build a menu listing components added to a page. These components will have an id and I will anchor-link to these so the page will scroll down to them respectively. The problem is i get them in scrambled order. If I laborate in...

Laravel 5, Derived table in join clause?

left-join,laravel-5,query-builder,derived-table
I have this query: SELECT * FROM blog LEFT JOIN ( SELECT blog_id, AVG(value) as blog_rating FROM blog_ratings GROUP BY (blog_id) ) T ON T.blog_id = blog.id; I do not know how to write this with Eloquent. For Example: Blog::select("*")->leftJoin( /* Here goes derived table */ )->get() How do I...

Laravel Query Builder: whereExists translates condition clause to question mark

laravel,query-builder
If I have the following query built using the Query Builder: $q = DB::table('Products')->whereExist(function ($q) { $q->select(DB::raw(1)) ->from('tags_products') ->where('products.PorductId', '=', 'tags_products.ProductID'); }); The translated SQL using $q->toSql(); that is: select * from `Products` where `exist` = (select 1 from `tags_products` where `products`.`ProductID` = ?) Apparently, the Query Builder translates tags_products.ProductID...

Datastax java-driver QueryBuilder.update issue in scala

scala,query-builder,datastax,datastax-java-driver
Here is the scala code I am attempting to use to update a row in a cassandra database: val query = QueryBuilder.update("twitter","tweets") .`with`(QueryBuilder.set("sinceid", update.sinceID) .and(QueryBuilder.set("tweets", update.tweets))) .where(QueryBuilder.eq("handle", update.handle)); which is based off of the suggestion here Everything seems to work correctly except for the ".and". The error I get back...

How to write this complex query in Laravel?

laravel,laravel-4,eloquent,laravel-5,query-builder
hello every one please how i can write this query : $laureats=Laureat::select('laureats.*') ->from(DB::raw(" SELECT laureats.nom FROM laureats,traces WHERE laureats.id=traces.laureat_id AND traces.etat_id=$etat and traces.etat_id=(select etat_id from traces where traces.laureat_id=laureats.id ORDER BY traces.dateTrace desc limit 1)")) ->get(); ...

Why I have Field dataAreaId Access denied in a Query?

axapta,x++,query-builder,dynamics-ax-2012
I have a problem, I used in my init Form a little Query. I used this code : query q = new Query(); QueryBuildDataSource qbds; QueryRun queryRun; qbds.addRange(fieldNum(MyTable,dataAreaId)).value(SysQuery::valueUnlimited()); queryRun = new queryRun(q); if (! queryRun.prompt()) { element.close(); } MyTable_ds.query(queryRun.query()); When I insered a value in my query prompt I will...