DAO

2018-07-13 09:30:55
tengfei
4994
Last edited by tengfei on 2019-09-16 14:11:37

1.  About DAO

zentaoPHP is not trying to implement ORM or ActiveRecord, because we believe that a framework should leave enough space for developers to play freely rather than do it all. Therefore, a simple and convenient DAO is provided in the framework. When we encapsulate DAO, we try to make it compatible with standard SQL writing.


The definitions of DAP are in lib/dao.class.php. When zentaoPHP is loaded, the $this->dao object is automatically generated, and $this->dao can be used directly in the code of control, model, or view to perform a variety of methods.


2. exec()

If it is a query statement, you need to use the fetch method to return data. Use the exec () method to update, delete, replace or so.


After executing DAO, you can use


fetch():         fetch the first record that meets the condition and return the format
fetch($filed):  fetch the value of $feild that meets the condition firstly
fetchAll():     fetch all records that meet the requirement and return in arrays with 0-n as index
fetchAll($key):  fetch all records that meet the condition and use $key as index
fetchPairs($key, $value): return the list of key-value pairs. If no parameter is specified, the first field returned is taken as key, the second as value.
fetchGroup($group, $key): group all records that meet the condition according to $group. For example, group all $status=active


3. Instruction

For the convenience of writing, DAO class encapsulates several operators:


eq: equal,
ne: not equal,
gt: greater than, 
lt: less than, 
in: in a list
between: between
notin:not in a list
like: fuzzy match


4. Query

Common query: search account=wwccss


$this->dao->select('*')
->from('user')
->where('account')
->eq('wwccss')
->fetch();


More complicated one query. Add andWhere (or orWhere)


$this->dao->select('*')->from('user')
->where('id')->gt(10)
->andWhere('age')->lt(20)
->orderBy('id desc')
->limit('1,10')
->fetchAll()


Left join query


$this->dao->select('t1.*, t2.*')->from('user')->alias('t1')->leftJoin('userGroup')->alias('t2')->on('t1.account = t2.account')->fetchAll();


Alternatives


$this->dao->findByAccount($account)->from('user')->fetch();               // a magic method, query by account
$this->dao->select('*')->from('user')->fetchAll('account');              // set account returned as key 
$this->dao->select('account, realname')->from('user')->fetchPairs();     // return the key-value pairs of account=>realname
$this->dao->select('class, account, realname')->from('user')->fetchGroup('class');     // group by class


Assemble SQL according to conditions: beginIF, FI()


$this->dao->select('*')->from('user')->where('id')->gt(10)->beginIF($class == 'online')->andWhere('status')->eq('online')->fi()->fetchAll();


5. INSERT

Use a data object to update. The key of the data object corresponds to the field name in the data table.


$user->account = 'wwccss';
$user->password = '123456';
$this->dao->insert('user')->data($user)->exec();


Or update one by one


$this->dao->insert('user')
->set('account')->eq($account)
->set('password')->eq($password)
->exec();


Echo the ID of the last inserted


echo $this->dao->lastInsertID();


6. UPDATE

It is similar to insert. Use a data object or a field to update.


$user->name = 'wwccss';
$user->age = 10;
$this->dao->update('user')->data($user)->where('id')->eq($userid)->limit(1)->exec(); 
$this->dao->update('user')
->set('account')->eq($account)
->set('password')->eq($password)
->exec()


7. REPLACE

A data object has to be defined for REPLACE too. Then call REPLACE. Note: make sure that the table has a major key or a unique index.


$this->dao->replace('user')->data($user)->exec();


8. DELETE


$this->dao->delete()->from('user')->where('id')->eq($userid)->exec();


Write a Comment
Comment will be posted after it is reviewed.