clancats / hydrahon Goto Github PK

hi,
I tried something like this
SELECT * FROM produtos WHERE qt < minqt AND id_user = 2

$Produto->select()
->where('id_user',$id_user)
->where('qt','<','minqt')
->get();

Wouldn't that be right?

->distinct('column_name')

It will have different result from

->addField(new \ClanCats\Hydrahon\Query\Expression('DISTINCT(colum_name)'))

I use the second one. I think the result need to be same, right?

SELECT * FROM doados WHERE date até date

After trying to install the package with composer, of course I copied an pasted the command from the documentation, afterwards getting this weird message:

$ php composer.phar require 'clancats/hydrahon'


  [InvalidArgumentException]
  Could not find package 'clancats/hydrahon'.

  Did you mean this?
      clancats/hydrahon

I don't know if it's related with my version of composer (it should be pretty recent), but I will make a PR to solve this because I don't think there's any version where the name without single quotes would cause an issue.

I didn't find any example of how to use arbitrary MySQL functions, it looks like only the provided ones like AVG, MIN and MAX can be used.

I tested sending a Func() object as the right hand side of a where clause and it failed due to param() in the translator checking for expressions but not functions.

It should also be possible to put a function as a SELECT parameter as well. This is useful for converting a datetime to a date for example when querying.

It's possible I missed the method and there just isn't an example highlighting this usage.
I did search the code however and only found Func() referenced in the aggregate functions.

So i was reading the Code Clean this times, and one topic that the book talk about is about unnecessary comments. I like so much of Hydrahon and i pretend contribute to its code. So in this weekend i've cloned the repo and decided to see if i can make the code a little cleaner.

What do you think about a PR removing this UNNECESSARY comments (Without be rude)? @mario-deluna

There is a way to execute the function like PDO::lastInsertId ?

I've noticed the whereIn method doesn't apply the "where" clause when the array is empty.

    public function whereIn($column, array $options = array())
    {
        // when the options are empty we skip
        if ( empty( $options ) )
        {
            return $this;
        }

        return $this->where($column, 'in', $options);
    }

I think this is kind of obscure behavior.

The current implementation of the method seems like doing this:

if (count($items) == 0) {
    $query->execute();
} else {
   $query->whereIn('item', $items)->execute();
}

I think the case scenario where you would want to do this is an exception rather than the norm, meaning I would not like it as a default behavior.

What happened in my project...

I thought of giving a real-life example to illustrate why this might be undesirable.

I'm working in a project where user is authenticated in one or more offices.
I ran a whereIn query to see if user_id appears in "offices" table.

When the user doesn't appear in any table, instead of getting a MySQL error, I get as a return all of the offices.

If the user is not well configured, I would rather see him having an empty screen (where in () is invalid SQL, so it would crash the app), rather than giving him access to all offices!

In conclusion

Dont' make whereIn ignore the second argument if an empty array is passed.

I can send a PR since I've already implemented this change in a commit of my fork of the repo.

Thanks for reading

Edit:

Another option would be that, if whereIn has an empty array, make it the SQL equivalent of where false.

I think that's a sensible default and wouldn't cause a MySQL error. After all, it's technically true that there should be no records if you pass an empty argument in such a query.

im trying to do

SELECT id , GROUP_CONCAT(name)
like this
select("id, GROUP_CONCAT(name) ")

but i getting problemes , how can i do it properly?

Hello there, how is it going?

I've run into a interesting puzzle trying to emulate the row_number function.

With raw SQL, I found how to do it in two ways:

select row_number as seqItem

from 

	(select
		`i`.`id`,
		@curRow := @curRow + 1 AS row_number
	from
		`orderitem` as `i`,
		(select @curRow := 0) as `r`
	where
		`i`.`idCompany` = '123'
		and `i`.`idOrder` = '456'
	) sub	

where sub.id = '789'; 

or

select row_number as seqItem

from 

	(select
		`i`.`id`,
		@curRow := @curRow + 1 AS row_number
	from
		`orderitem` as `i`
	inner join
		(select @curRow := 0) as `r`
	where
		`i`.`idCompany` = '123'
		and `i`.`idOrder` = '456'
	) sub	

where sub.id = '789'; 

I've tried doing the following

$subSelect = $qb->table(['orderitem' => 'i', '(select @curRow := 0)' => 'r'])->select('i.id', $qb->raw('@curRow := @curRow + 1 AS row_number'))
		  ->where('i.idCompany', $idCompany)
		  ->where('i.idOrder', $idOrder);

$qb = $qb->table(['sub' => $subSelect])->select(['row_number' => 'seqItem'])->where('sub.id', $idOrderItem);

which resulted in the query string

select
	`row_number` as `seqItem`
from
	(
	select
		`i`.`id`,
		@curRow := @curRow + 1 AS row_number
	from
		`orderitem` as `i`,
		`(select @curRow := 0)` as `r`
	where
		`i`.`idCompany` = '123'
		and `i`.`idOrder` = 456) as `sub`
where
	`sub`.`id` = 789;

and it resulted in a error because of the backticks in

`(select @curRow := 0)`

Is this not supported, or I'm just missing something?

Best regards!

Is there any possibility to build a query which uses a join with subselect, eg:

$t2 = $qb->table('table2')->select(array('t2.id'));
$qb->table('table1 as t1')->select()->join(array('t2' =>$2));

The code above would translate to:

select * from table1 as t1 join (select id from table2) as t2 on t2.id = t1.ref

Hello ClanCats, first of all thank you for this wonderful library.

I was wondering how can I write "BETWEEN" condition using this library. I have tried this code -
$q->where(new Expression('start_time BETWEEN "'.$_POST['start_time'].'" AND "'.$_POST['end_time'].'" '));

But it is generating the query like this -
start_time BETWEEN "<start_time>" AND "<end_time>" = ?

I just want to know how can I write raw query inside of where clause ? Just like laravel "whereRaw()".
If this is not possible please implement it, because it is very important feature for this library.
Thanks.

function fetch($db, $translator, $wheres)
{
	$query = new \ClanCats\Hydrahon\Query\Sql\Select;
	/* other stuff to setup query */
	$query->where($wheres);
	list($queryString, $queryParams) = $translator->translate($query);
	return getQueryResult($db, $queryString, $queryParams);
}

fetch($mysqli, $translator, [ 'otherThing' => 'otherValue', 'status' => [ 'Active', 'Inactive' ] ]);

The above code generates a MySQL error: "Operand should contain 1 column(s)"

Examining the generated query string:

select fields from table where ( `otherThing` = ?, `status` = (?, ?) )

The Fix:

Replace \ClanCats\Hydrahon\Query\Sql\SelectBase.php:153-156:

        if (is_array($param2)) 
        {
            $param2 = array_unique($param2);
        }

With:

        if (is_array($param2)) 
        {
            $param2 = array_unique($param2);
            if ($param1 === '=') $param1 = 'IN';
        }

I'm using PhpStan over my project and it has highlighted some incorrect type hinting.

Specifically SelectBase's where() functions specify the $column must be a string, however it could be an array or a callable as well which makes PhpStan unhappy.

Hi
I'm not sure if I'm doing something wrong or not but I get this error In Phpstorm and PHP error log.
Also, I searched the Builder.php file and found no reference to the table function inside it.
Please tell me what to do
Thanks.

Hey @mario-deluna or other folk who might be happening by,
I was wondering how it would be possible to catch PDO-Exceptions using Hydrahon. My amateurish attempts at just wrapping the PDO-implementation in try/catch didn't yield any results. I'm using this tool for a project where I wanted to build SELECT statements "iteratively" depending on the user-supplied search-model and it seemed perfect for my requirements.

I simply tried this:

public function getQueryBuilder()
  {
    $conn = $this->connection;
    $builder = new \ClanCats\Hydrahon\Builder(
      'mysql',
      function ($query, $queryString, $queryParameters) use ($conn) {
        try {
          $statement = $conn->prepare($queryString);
          $statement->execute($queryParameters);
        } catch (Exception $e) {
          error_log('Exception in DBConnector class');
          throw new Exception('Error executing DB Query', 0, $e);
        }

        if ($query instanceof \ClanCats\Hydrahon\Query\Sql\FetchableInterface) {
          return $statement->fetchAll(\PDO::FETCH_ASSOC);
        }
      });
    return $builder;
  }

Any hints would be appreciated.

• If you pass more than one instances of Expression as parameters for orderBy()
• If you are using multiple parameters for orderBy() and one of them is Expression

The following error occurs:

Illegal offset type on /clancats/hydrahon/src/Query/Sql/Select.php at line 284.

Does Hydrahon get the related tables (Foreign key) of a selected column?

This sql query is not running on mysql 8 because the * is wrapped with `

Hi,

I'd like to know if it's possible to do a select of a select, e.g:

SELECT 
    *
FROM
    (SELECT 
        *
    FROM
        contact) AS b
ORDER BY name ASC

After some research I found that Doctrine DBAL does something like this.
If there is no possibility, is there any way that I can get the query string without having to call execute()?

I am not able to reproduce following sql using HYDRAHON

SELECT a.idproduto AS id ,b.nome AS nome ,b.valor AS valor ,a.qauntidade AS qauntidade FROM quantos a INNER JOIN produtos b ON a.idproduto = b.id where a.idcliente = 37

Hi,

I belive there is a problem with the run() method implemented in Select.php.
The function seems to be called from one-result helpers, and it should return the query result.

The method executes the query calling executeResultFetcher() which returns a mysqli object, then tests if it is an array, if not returns an empty array.
Shoudn't it fetch the results first then test if it is an array, instead of testing the object?

for example the following query always returns [] as the result:

table('table1')
->select([
'table1.field1',
'table2.filed1',
'table3.field1'
])
->join('table2', 'table1.id', '=', 'table2.table1_id')
->join('table3', 'table2.id', '=', 'table3.table2_id')
->get();

i used this in my project

Hello,
I came across this class but could not find where are php example? i found the src folder there is no example as to which file should be included on top of my application.
Please make a sample php script showing how this class is used for basic "select" and "read" and the user can follow after .

Thank you

Transaction is useful when updating money record. And i wish its added on the next release.

Currently when inserting, updating and deleting there is no return value at all.

This should be trivial to add, by supplementing the FetchableInterface with a InsertableInterface (could return an array containing both insert id and rows affected) and ModifyableInterface (returning the rows affected).

This change would be backwards compatible so if the feature isn't used then no changes to the fetcher function are required.

I could provide a PR for this feature if it would be helpful?

I have several statements that require the use of COUNT and SUM in my project. Since the library parses my input, I have to use the raw(expression) function which solves the problem, but it doesn't look good in the code.

Edit:
There's a function count() but it doesn't let you choose the column..

Hi!

I love a lot this library and I want just to ask if it would be possible to add a new method to \ClanCats\Hydrahon\Query\Sql\Select (and eventally related) to clear current orders? For example there is a way to drop fields, but it would be nice to drop also current order - my use case is a repository with default orders later on configured by an object, but I'm not able to clear default order and then respect just one from the configuration.

This looks like a simple adjustment, so the question is if it's worth your time, or if it could go into v2 or even... is still there an active development :) ?

Thanks for response!

I would like to propose support for the having clause.

I managed to pull out a working demo in this fork of the repo but found some issues:

1. The Mysql::translateWhere() function has two responsibilities, and it's confusing
2. The having() methods (and helper aliases) was created in the SelectBase class, to keep support for nested having clauses. These methods should be placed inside the Select clause instead (nested sub-queries should be created as Select objects, to have the having() method available).
3. There's no validation (you can use having without a group by). Is not clear if validation should occur in the Mysql class, or if the having() method should throw exception if groupBy() wasn't called previously

I would like to hear some opinions on 3. For the moment I'm not implementing any validation whatsoever, because:

• For once, it would be annoying to have an exception thrown if you attempt to use having() without calling groupBy() first. It probably should not depend on the order (user might want to add it later for whatever reason?).
• Other alternative would be to throw the exception in the ->get() function or in the translator, but I'm not clear those functions should have that responsibility.

In an unrelated topic, please let me know if you consider the PR I send to add support for the whereNotIn() method is good enough to be approved, I haven't done a PR to a big project like this one before, and I wonder if it meets the standard (I also wonder why it doesn't build, I don't understand very well "Travis"). I will consider feedback from this PR to make a future PR with this feature.

Thanks

the insert query builder string returned "insert into people (age, name) values (?, ?), (?, ?), (?, ?)" with question mark!
in Mysql.php param($value) is returning a question mark! i replace it with "$value" then the query syntax was correct.

to fix this either to return $value insted question mark in param($a) function body or to return the $value from addParameter($a) function.

Hello,

When using the subquery in the developer branch there seems to be an issue with the select function.

for example:

$ret = $builder->select(["a" => function ($q) {
    $q->table("table2");
    $q->select(["column1", "column2"]);
});

Returns: There is no macro or method with the name "select" registered.

However as a workaround, if select is changed to fields it works fine, is this intended?

Kind Regards,
Joe

how would this consultation with the hydrahon be?

SUM(+1) as matches,
SUM(IF((matches.playerblue = '.$idPlayer.' AND matches.rtb > matches.rtr) OR (matches.playerred = '.$idPlayer.' AND matches.rtr > matches.rtb), 1, 0)) as victories,
SUM(IF((matches.playerblue = '.$idPlayer.' AND matches.rtb < matches.rtr) OR (matches.playerred = '.$idPlayer.' AND matches.rtr < matches.rtb), 1, 0)) as defeats,
SUM(IF((matches.playerblue = '.$idPlayer.' AND matches.rtb = matches.rtr) OR (matches.playerred = '.$idPlayer.' AND matches.rtr = matches.rtb), 1, 0)) as drowns,
SUM(IF(matches.playerblue = '.$idPlayer.', matches.rtb, 0) + IF(matches.playerred = '.$idPlayer.' , matches.rtr, 0)) as goalscored,
SUM(IF(matches.playerblue = '.$idPlayer.', matches.rtr, 0) + IF(matches.playerred = '.$idPlayer.' , matches.rtb, 0)) as concededgoals,
WHERE matches.playerblue = '.$idPlayer.' OR matches.playerred = '.$idPlayer.'
LIMIT '.$games

The title speaks for him self. I need to update the main readme so that the most important information is covered.

Good night,

How i can get variable from function where.
Example:

where(function($q) {
$q->where('start','>=',$variable1)->orWhere('end','<=',$variable2);
});

Hello,

First of all, congratulations for this very useful project. However, while developing an ORM based on this module, I need to use a MySQL function (and more precisely DATABASE()) in a Where clause of a query.

For the record, it's a query in the informations_schema database to get the list of columns of a table and then adapt the creation of my object accordingly.

Anyway, I logically did something like this to get my list of columns:

        $fields = $h->table("information_schema.columns")
            ->select(["column_name", "column_type"])
            ->where("table_schema", new Func("DATABASE"))
            ->where("table_name", "my_tablename")
            ->get();

Except that I realized that the new Func("DATABASE") object was not translated into SQL, which inevitably crashes PDO when applying the parameters of the request to its execution.

For all those who would have this problem, and for a future update of the module, I can propose the following correction which works for me:

// In the MySQL translator class, I modified the param() method as follows :
    /**
     * creates an parameter and adds it
     *
     * @param mixed $value
     * @return string
     */
    protected function param($value)
    {
        if (!$this->isExpression($value)) {
            // ************************ START HERE ************************
            if ($value instanceof Func)
                // If the value of the parameter is an instance of the Func class, then it must be inserted as is after
                // escaping in the query, otherwise it will be escaped as a string when the query is executed.
                return $this->escapeFunction($value);
            // ************************* END HERE *************************
            $this->addParameter($value);
            return '?';
        }

        return $value;
    }

I don't know if this was the best way to do it, but being able to use MySQL functions in Where clauses seems to me indispensable at least for some uses (which are probably not limited to my case alone). That's why I propose this modification. If I have time, I'll see to make a pull request, but in the meantime I gave you my solution here.

I hope this report and my proposed solution have been useful to you.

Regards,

Hydrahon returns a two dimensional array. But give no quick option to get first()

select query with alias auto order by id asc with no alias

select * from hm2_user_balances as ub inner join hm2_users as u on u.id = ub.user_id where u.ref = ? and ub.type = ? and ub.amount > ? order by RAND() asc, id asc limit 0, 1

Hi,

Is there any way to run raw queries using Hydrahon?

I want to truncate a table, but I haven't found a function in Hydrahon, and also haven't found a way to run a raw query, is it possible?

Also, is there any way to create tables using Hydrahon? It could be using raw queries as I wanna do to truncate a table.

Thank you in advance.

Hello. I'm wondering if it's possible to use Hydrahon to output a query without having to supply an extant db connection.

Thank you.

I was doing a charge system for my company. And i haved that verify if the client X already haved a charge created in the respectly YearMonth (YYYYMM).

I did the query and i asked to hydrahon class fetch all payments charges, order by (ID, DESC), and bring the first result. But instead of he give me first result of ordering, he bringed to me first result before ordering!

This was the query methods:

This was the result he bring me.

This is what he need bring me:

Is there a possibility to create queries with the UNION or UNION ALL operator?

Something like this would be useful:

$h->table('students')
  ->select('column_name')
  ->union()
  ->table('teachers')
  ->select('column_name')
  ->get();

Resulting in:

SELECT column_name FROM students
UNION
SELECT column_name FROM teachers;

Heya!

I find it frustrating while creating multiple bulk requests because:

🦀 🦀

$q->where([array][0],'like', null, 'or');
$q->where([array][1], 'like', null, 'and');

select 'cars.*' from 'cars' where ('cars'.'title' = '%volvo%' or 'cars'.'slug' = '%volvo%') and ( 'cars'.'title' = '%AUX%' and 'cars'.'slug' = '%AUX%')

This builds first bulk as 'or' and the other bulk as 'and' with an and between because the last bulk is and, switch the wheres and it's 'and' 'or' 'or'

😖

My quickfix locally to get this to work is that all bulk columns have only the relation of 'or' in this case.

i would love to be able to use both or and and like :)

$q->where([array][0],'like', null, 'or');
and
$q->where([array][1], 'like', null, 'or');

select 'cars.*' from 'cars' where ('cars'.'title' like '%volvo%' or 'cars'.'slug' like '%volvo%') and ( 'cars'.'title' = '%AUX%' or 'cars'.'slug' = '%AUX%')

https://github.com/ClanCats/Hydrahon/blob/master/src/Query/Sql/SelectBase.php#L126

My modified worthless version:

$subquery->where($key, ($param1) ? $param1:null, ($param1) ? $val:null, 'or');

I was trying to go through the hydrahon documentation yesterday and it took me sometime to connect the dot, as the first initialization of hydrahon uses $hydrahon and later in the documentation just $h was used. Which was sort of confusing..

class Person { private $id; private $name; private $age; }

Hi. I want to mapping the columns of the result set to named properties in the class. Example:

$h->table('person') ->select() ->where('name', 'George') ->get(**Person::class**);

Can you add such a feature in future releases?

Can anyone point me at how to check the result from an UPDATE

Hi,

How can I increase an integer value on update?

LG Matix