Magento2 数据库操作

偏执的太偏执、 2022-10-02 01:57 28阅读 0赞

[2019独角兽企业重金招聘Python工程师标准>>> ][2019_Python_] ![hot3.png][]

### **直接操作数据库** ###

$objectManager = \\Magento\\Framework\\App\\ObjectManager::getInstance();  
$resource = $objectManager->get('Magento\\Framework\\App\\ResourceConnection');  
$connection = $resource->getConnection();  
$tableName = $resource->getTableName('employee');

**Select Data from table**  
$sql = "Select \* FROM " . $tableName;  
$result = $connection->fetchRow($sql); //get one info，返回二维数组  
$result = $connection->fetchAll($sql);   //get all info

**Delete Data from table**  
$sql = "Delete FROM " . $tableName." Where emp\_id = 10";  
$connection->query($sql);

**Insert Data into table**  
$sql = "Insert Into " . $tableName . " (emp\_id, emp\_name, emp\_code, emp\_salary) Values ('','XYZ','ABD20','50000')";  
$connection->query($sql);

**Update Data into table**  
$sql = "Update " . $tableName . "Set emp\_salary = 20000 where emp\_id = 12";  
$connection->query($sql);

### **addFieldToFilter** ###

protected  $\_productCollectionFactory ;  
public  function \_\_construct (  
        \\Magento\\Catalog\\Model\\ResourceModel\\Product\\CollectionFactory  $productFactory  
     )  \{   
         $this \->\_productCollectionFactory  =  $productFactory ;  
     \}  
     public  function getProductCollection ( )  
     \{   
       return    $this \->\_productCollectionFactory \-> create ( ) \-> addAttributeToSelect ( '\*' ) \-> addFieldToFilter ( 'sku’,'test ');  
       
     \}

#### **Equal: eq** ####

Now we use equal to filter production collection.

$this \->\_productCollectionFactory \-> addFieldToFilter ( 'status' ,  [array][] ( 'eq'  =>  1 ) ) ;  // Using the operator  
$this \->\_productCollectionFactory \-> addFieldToFilter ( 'status' ,  1 ) ;  // Without using the operator

#### **Not Equals – neq** ####

Now we use not equal to filter production collection.

$this \->\_productCollectionFactory \-> addFieldToFilter ( 'sku' ,  [array][] ( 'neq'  =>  'test-product' ) ) ;

#### **Like – like** ####

Now we use Like to filter production collection.

$this \->\_productCollectionFactory \-> addFieldToFilter ( 'sku' ,  [array][] ( 'like'  =>  'UX%' ) ) ;

#### **Not Like – nlike** ####

Now we use not like to filter production collection.

$this \->\_productCollectionFactory \-> addFieldToFilter ( 'sku' ,  [array][] ( 'nlike'  =>  'err-prod%' ) ) ;

#### **In – in** ####

Now we use In to filter production collection.

$this \->\_productCollectionFactory \-> addFieldToFilter ( 'entity\_id' ,  [array][] ( 'in'  =>  [array][] ( 1 , 4 , 98 ) ) ) ;

#### **Not In – nin** ####

Now we use not In to filter production collection.

$this \->\_productCollectionFactory \-> addFieldToFilter ( 'entity\_id' ,  [array][] ( 'nin'  =>  [array][] ( 1 , 4 , 98 ) ) ) ;

#### **NULL – null** ####

Now we use null to filter production collection.

$this \->\_productCollectionFactory \-> addFieldToFilter ( 'description' ,  [array][] ( 'null'  =>  true ) ) ;

#### **Not NULL – notnull** ####

Now we use not null to filter production collection.

$this \->\_productCollectionFactory \-> addFieldToFilter ( 'description' ,  [array][] ( 'notnull'  =>  true ) ) ;

#### **Greater Than – gt** ####

Now we use greater than to filter production collection.

$this \->\_productCollectionFactory \-> addFieldToFilter ( 'entity\_id' ,  [array][] ( 'gt'  =>  5 ) ) ;

#### **Less Than – lt** ####

Now we use less than to filter production collection.

$this \->\_productCollectionFactory \-> addFieldToFilter ( 'entity\_id' ,  [array][] ( 'lt'  =>  5 ) ) ;

#### **Greater Than or Equals To- gteq** ####

Now we use greater than to filter production collection.

$this \->\_productCollectionFactory \-> addFieldToFilter ( 'entity\_id' ,  [array][] ( 'gteq'  =>  5 ) ) ;

#### **Less Than or Equals To – lteq** ####

Now we use less than equal to filter production collection.

$this \->\_productCollectionFactory \-> addFieldToFilter ( 'entity\_id' ,  [array][] ( 'lteq'  =>  5 ) ) ;

**打印SQL语句**

$collection = $this->\_order->getCollection()->addFieldToFilter('created\_at',array('like' => '2018-10%'));  
echo $collection->getSelect()->\_\_toString(); //打印sql语句

**打印getCollection结果数量**

$collection = $this->\_order->getCollection()->addFieldToFilter('created\_at',array('like' => '2018-10%'));  
echo $collection->count(); //打印sql语句

转载于:https://my.oschina.net/ganfanghua/blog/3029100

[2019_Python_]: https://my.oschina.net/u/2663968/blog/3061697
[hot3.png]: /images/20220113/a80922b79ad143948c58f7170c23e63b.png
[array]: http://www.php.net/array