New Case Study:See how Anthropic automated 95% of dependency reviews with Socket.Learn More →
Socket
Sign inDemoInstall
Socket
Sign inDemoInstall

@krishnapawar/kp-mysql-models

Package Overview
Dependencies
Maintainers
0
Versions
15
Alerts
File Explorer

Advanced tools

License
Socket logo

Install Socket

Detect and block malicious and high-risk dependencies

Install

@krishnapawar/kp-mysql-models - npm Package Compare versions

Comparing version 1.5.0 to 1.5.2

lib/ORMManager.js

219

lib/BaseModels.js

@@ -17,3 +17,2 @@ const {

class BaseModels extends QueryBuilder {
#list = [];
constructor(setting = "") {

@@ -85,3 +84,3 @@ super();

if (response) {
return collect(resolve({ response, paginate }));
return resolve({ response, paginate });
}

@@ -96,3 +95,3 @@ }

if (response) {
return collect(resolve(response));
return resolve(response);
}

@@ -512,7 +511,8 @@ }

}
if (data.elements != undefined && data.elements != null) {
if ((data.elements != undefined && data.elements != null) || isObject(data)) {
var sqlQuery = `UPDATE ${this.thisTable()} ${getKeyValue(
data.elements,
data.elements ?? data,
"SET"
)} ${whereClause(data)}`;
)} ${whereClause(this.#getQuery(data))}`;
this._connection.query(sqlQuery, (err, res) => {

@@ -544,3 +544,3 @@ if (err) {

if (x.elements != undefined && x.elements != null) {
const checkQuery = `SELECT COUNT(*) AS count FROM ${this.thisTable()} ${whereClause(x)}`;
const checkQuery = `SELECT COUNT(*) AS count FROM ${this.thisTable()} ${whereClause(this.#getQuery(x))}`;

@@ -552,3 +552,3 @@ this._connection.query(checkQuery, (err, result) => {

const recordExists = whereClause(x) != "" ? result[0].count > 0 : false;
const recordExists = whereClause(this.#getQuery(x)) != "" ? result[0].count > 0 : false;

@@ -558,3 +558,3 @@ let sqlQuery;

if (recordExists) {
sqlQuery = `UPDATE ${this.thisTable()} ${getKeyValue(x.elements, "SET")} ${whereClause(x)}`;
sqlQuery = `UPDATE ${this.thisTable()} ${getKeyValue(x.elements, "SET")} ${whereClause(this.#getQuery(x))}`;
} else {

@@ -587,15 +587,14 @@ const keys = Object.keys(x.elements).join(", ");

}
if (data.where != undefined) {
this._connection.query(
`DELETE FROM ${this.thisTable()} ${whereClause(data)}`,
(err, res) => {
if (err) {
return reject(err);
}
if (res) {
return resolve(res);
}
if (isEmpty(whereClause(this.#getQuery(data)))) return reject("Where condition is required!");
this._connection.query(
`DELETE FROM ${this.thisTable()} ${whereClause(this.#getQuery(data))}`,
(err, res) => {
if (err) {
return reject(err);
}
);
}
if (res) {
return resolve(res);
}
}
);
});

@@ -609,15 +608,14 @@ };

}
if (data.where != undefined) {
this._connection.query(
`DELETE FROM ${this.thisTable()} ${whereClause(data)}`,
(err, res) => {
if (err) {
return reject(err);
}
if (res) {
return resolve(res);
}
if (isEmpty(whereClause(this.#getQuery(data)))) return reject("Where condition is required!");
this._connection.query(
`DELETE FROM ${this.thisTable()} ${whereClause(this.#getQuery(data))}`,
(err, res) => {
if (err) {
return reject(err);
}
);
}
if (res) {
return resolve(res);
}
}
);
});

@@ -631,3 +629,3 @@ };

}
this._connection.query(`DELETE FROM ${this.thisTable()} ${whereClause(data)}`, (err, res) => {
this._connection.query(`DELETE FROM ${this.thisTable()} ${whereClause(this.#getQuery(data))}`, (err, res) => {
if (err) {

@@ -662,3 +660,3 @@ return reject(err);

if (isEmpty(this._softDelete)) return reject("this._softDelete is not true!");
if (isEmpty(whereClause(data))) return reject("Where condition is required!");
if (isEmpty(whereClause(this.#getQuery(data)))) return reject("Where condition is required!");
try {

@@ -670,3 +668,3 @@ if (sqlConnect(this._connection)) {

if (colunm) {
this._connection.query(`UPDATE ${this.thisTable()} SET deleted_at = NOW() ${whereClause(data)}`, (err, res) => {
this._connection.query(`UPDATE ${this.thisTable()} SET deleted_at = NOW() ${whereClause(this.#getQuery(data))}`, (err, res) => {
if (err) {

@@ -696,3 +694,3 @@ return reject(err);

if (colunm) {
this._connection.query(`UPDATE ${this.thisTable()} SET deleted_at = NOW() ${whereClause(data)}`, (err, res) => {
this._connection.query(`UPDATE ${this.thisTable()} SET deleted_at = NOW() ${whereClause(this.#getQuery(data))}`, (err, res) => {
if (err) {

@@ -719,4 +717,4 @@ return reject(err);

if (isEmpty(this._softDelete)) return reject("this._softDelete is not true!");
if (isEmpty(whereClause(data))) return reject("Where condition is required!");
this._connection.query(`UPDATE ${this.thisTable()} SET deleted_at = NULL ${whereClause(data)}`, (err, res) => {
if (isEmpty(whereClause(this.#getQuery(data)))) return reject("Where condition is required!");
this._connection.query(`UPDATE ${this.thisTable()} SET deleted_at = NULL ${whereClause(this.#getQuery(data))}`, (err, res) => {
if (err) {

@@ -738,3 +736,3 @@ return reject(err);

if (isEmpty(this._softDelete)) return reject("this._softDelete is not true!");
this._connection.query(`UPDATE ${this.thisTable()} SET deleted_at = NULL ${whereClause(data)}`, (err, res) => {
this._connection.query(`UPDATE ${this.thisTable()} SET deleted_at = NULL ${whereClause(this.#getQuery(data))}`, (err, res) => {
if (err) {

@@ -749,2 +747,22 @@ return reject(err);

}
clearTrash(data) {
if (isTable(this.thisTable())) return reject(isTable(this.thisTable()));
return new Promise((resolve, reject) => {
if (isEmpty(this._softDelete)) return reject("this._softDelete is not true!");
if (sqlConnect(this._connection)) {
return reject(sqlConnect(this._connection));
}
this._connection.query(
`DELETE FROM ${this.thisTable()} ${whereClause(this.#getQuery(Object.assign(data??{},{whereNotNull:["deleted_at"]})))}`,
(err, res) => {
if (err) {
return reject(err);
}
if (res) {
return resolve(res);
}
}
);
});
};
thisTable() {

@@ -785,114 +803,10 @@ return isEmpty(this._table) ? this.constructor.name.replace(/([A-Z])/g, '_$1').toLowerCase().slice(1)+'s' : this._table;

}
#handleRelation(t,m,n,cb = false) {
const tc = isObject(t) ? t : { table: t };
if (cb && isObject(cb(this))) {
const itemW = cb !== false ? cb(this).condition : {};
const it = cb !== false && cb(this).#list && cb(this).#list[0] ? {w:cb(this).#list[0]} : {};
let tran =this.transform({ method: m, data: { ...tc, ...it, ...n } });
const keys = Object.keys(tran);
const values = Object.values(tran);
// this.#list =[];
// Use computed property names to dynamically add the key-value pair to the object
if(itemW.with){
values[0].with = {
...values[0].with,
...itemW.with,
}
}
this.#list.push({ method:keys[0],data: {...values[0]} });
} else {
this.#list.push({ method: m, data: { ...tc, ...n } });
setQuery(x) {
x.with = {
...this.getConfigList(),
...x.with,
...this.x.with,
}
return this;
}
hasMany(t, x, cb = false) {
const methodName = (new Error()).stack.split('\n')[2].trim()
.split(' ')[1].split('.')[1];
return this.#handleRelation(t,methodName,{hasMany: x},cb)
}
this.x = x;
belongsTo(t, x, cb = false) {
const methodName = (new Error()).stack.split('\n')[2].trim()
.split(' ')[1].split('.')[1];
return this.#handleRelation(t,methodName,{belongsTo: x},cb)
}
belongsToMany(t, x, cb = false) {
const methodName = (new Error()).stack.split('\n')[2].trim()
.split(' ')[1].split('.')[1];
return this.#handleRelation(t,methodName,{belongsToMany: x},cb)
}
connect(t, x, cb = false) {
const methodName = (new Error()).stack.split('\n')[2].trim()
.split(' ')[1].split('.')[1];
return this.#handleRelation(t,methodName,{connect: x},cb)
}
hasOne(t, x, cb = false) {
const methodName = (new Error()).stack.split('\n')[2].trim()
.split(' ')[1].split('.')[1];
return this.#handleRelation(t,methodName,{hasOne: x},cb)
}
transform(input) {
try {
// Base case: if there's no 'method' or 'data' field, return null
if (!input || !input.method || !input.data) return input;
// Extract method and data
const { method, data } = input;
// Create the base structure for the output
let output = {
[method]: {
table: data.table,
hasOne: data.hasOne || {},
}
};
// Check if there's nested data (i.e., the 'w' field)
if (data.w) {
const nestedMethod = data.w.method;
output[method].with = {
[nestedMethod]: this.transform(data.w)[nestedMethod]
};
}
return output;
} catch (error) {
return error;
}
}
async setQuery(x, seen = new Set()) {
const processList = async (list) => {
if (!list || list.length === 0) return {};
const results = await Promise.all(list.map(async (item) => {
const result = {};
result[item.method] = item.data;
if (item.w && !seen.has(item.w)) { // Ensure no infinite recursion
seen.add(item.w); // Mark this item as seen
result[item.method].with = await this.get(item.w, seen);
}
return result;
}));
return results.reduce((acc, item) => Object.assign(acc, item), {});
};
if (this.#list && this.#list.length > 0) {
x.with = await processList(this.#list);
}else{
x.with = {
...x.with,
...this.x.with,
}
this.x = x;
}
return this.x;

@@ -902,3 +816,5 @@ }

#getQuery(q,check=true){
Object.assign(this.x , q);
if(!isEmpty(q)){
Object.assign(this.x , q);
}
if (check) {

@@ -909,2 +825,3 @@ this.setQuery(this.x);

this.x = {};
this.setConfigList({});
return x;

@@ -911,0 +828,0 @@ }

18

lib/QueryBuilder.js

@@ -1,4 +0,8 @@

class QueryBuilder{
const ORMManager = require('./ORMManager');
class QueryBuilder extends ORMManager{
constructor() {
super();
this.x = {};
this._softDelete = false;
}

@@ -37,2 +41,8 @@ table(x) {

}
withoutTrashed() {
if(this._softDelete){
Object.assign(this.x,{whereNotNull:['deleted_at']});
}
return this;
}
when(c,cb) {

@@ -86,6 +96,2 @@ if(c){

}
with(c){
this.x.with = c;
return this;
}
pagination(x) {

@@ -131,3 +137,3 @@ this.x.pagination = x.currentPage;

buildQuery(d=false) {
if(d) return this.x;
if(d) return Object.assign(this.x,{with:{...this.getConfigList()}});
return this;

@@ -134,0 +140,0 @@ }

33

lib/sqlHandler.js

@@ -457,19 +457,22 @@ const {

try {
const colunm = await dbQuery(
connection.query(
`SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '${table}' ORDER BY ORDINAL_POSITION DESC LIMIT 1`,
connection
(err1, colunm) => {
if (err1) return reject(err1);
if (colunm.length) {
connection.query(
`ALTER TABLE ${table} ADD deleted_at TIMESTAMP NULL DEFAULT NULL AFTER ${colunm[0].COLUMN_NAME}`,
(err, res) => {
if (err) {
return reject(err);
}
return resolve(true);
}
);
} else {
return resolve(false);
}
}
);
if (colunm.length) {
connection.query(
`ALTER TABLE ${table} ADD deleted_at TIMESTAMP NULL DEFAULT NULL AFTER ${colunm[0].COLUMN_NAME}`,
(err, res) => {
if (err) {
return reject(err);
}
return resolve(true);
}
);
} else {
return resolve(false);
}
} catch (error) {

@@ -476,0 +479,0 @@ return reject(error);

31

package.json
{
"name": "@krishnapawar/kp-mysql-models",
"version": "1.5.0",
"description": "The `kp-mysql-models` library simplifies MySQL database interaction, streamlining tasks such as creating, inserting, updating, and deleting records, as well as handling complex queries like joins, pagination, and conditional operations. By offering an intuitive and efficient approach, it significantly reduces development time and effort.",
"version": "1.5.2",
"description": "`kp-mysql-models` is a lightweight, easy-to-use, and promise-based Node.js ORM tool designed for MySQL. It simplifies database interactions by offering features like model-based query building, CRUD operations, and support for advanced queries such as joins, pagination, and relationships. It is a powerful alternative to Sequelize. it significantly reduces development time and effort",
"main": "lib/index.js",

@@ -14,5 +15,22 @@ "scripts": {

"keywords": [
"kp-mysql-models","@krishnapawar/kp-mysql-models","mysql-models-builder","model","mysql models","mysql","query","builder","model","mysql models","mysql query builder","query builder","mysql builder","kp","krishna pawar","krishnapawar"
"kp-mysql-models",
"@krishnapawar/kp-mysql-models",
"mysql-models-builder",
"model",
"mysql models",
"mysql",
"query",
"builder",
"mysql query builder",
"query builder",
"promise-based ORM",
"Node.js ORM",
"MySQL ORM",
"Sequelize alternative",
"easy ORM",
"database models",
"krishna pawar",
"kp"
],
"author": "krishna pawar <krishnapawar90906@gmail.com>",
"author": "Krishna Pawar <krishnapawar90906@gmail.com>",
"license": "MIT",

@@ -22,3 +40,6 @@ "bugs": {

},
"homepage": "https://github.com/krishnapawar/kp-mysql-models#readme"
"homepage": "https://github.com/krishnapawar/kp-mysql-models#readme",
"engines": {
"node": ">=12.0.0"
}
}

1120

README.md
# kp-mysql-models
>The `kp-mysql-models` is a mysql query builder light weight library that simplifies interactions with MySQL databases. It streamlines tasks such as creating, inserting, updating, and deleting records, and handles complex operations like joins, pagination, and conditionals. Its intuitive and efficient approach can greatly expedite development, saving both time and effort.
>The `kp-mysql-models` is a mysql query builder light weight library that simplifies interactions with MySQL databases and it's promise-based Node.js ORM tool. It streamlines tasks such as creating, inserting, updating, and deleting records, and handles complex operations like joins, pagination, and conditionals. Its intuitive and efficient approach can greatly expedite development, saving both time and effort.
---
## Installation And Import it into your project
```bash

@@ -21,10 +24,21 @@ npm i kp-mysql-models

## Usage
This package provides a set of MySQL model for working with MySQL database. It is built on top of the `mysql` npm module
## Features
` Note:- ` for connection mysql we must have use library `mysql` for example.
- Intuitive CRUD operations: Create, Read, Update, and Delete.
- Advanced relationship handling: `hasOne`, `belongsTo`, `hasMany`.
- Built-in soft delete functionality.
- Dynamic pagination and filtering.
- Simplified advanced joins.
- Comprehensive helper methods for efficiency.
```JavaScript
var mysql = require("mysql");
var pool = mysql.createPool({
---
## Setting Up
### MySQL Connection
Ensure you have a MySQL connection set up using the `mysql` library:
```javascript
const mysql = require("mysql");
const pool = mysql.createPool({
connectionLimit: 10,

@@ -36,39 +50,21 @@ host: "localhost",

});
```
## Basic Usage Examples
After configuring the connection, create model using table and db connection:
```JavaScript
### Basic Model Setup
Create a model for the `users` table:
```javascript
const { BaseModels } = require("@krishnapawar/kp-mysql-models");
let User = new BaseModels({ _table: "users", _connection: pool });
const User = new BaseModels({
_table: "users",
_connection: pool,
});
```
// Retrieve a single record
let data = await User.first(); // Retrieves the first user record
let data = await User.where({ where: { id: req.body.id } }).first();
//or use like this
let data = await User.first({ where: { id: req.body.id } });
### Defining Models
// Retrieve all records
let data = await User.get(); // Retrieves all records
let data = await User.where("id", 1).get();
//or
let data = await User.get({where:{"id":1}});
Create model classes by extending `BaseModels` for better organization and customization:
// Delete a record
let data = await User.where("id", 585).delete();
//or
let data = await User.delete({ where: { id: 585 } });
// Truncate the table
let data = await User.truncate();
```
## Defining Models
Create model classes by extending BaseModels for each table, with optional customizations for table name and connection settings.
>To align with the instructions for creating a class named after the table (singular form) and using it in your controller, without explicitly connecting to the table name when the class name matches the table name (minus the "s"), you can rewrite the given JavaScript code as follows:
### Steps
### Example Steps 1
1 Define the User Class: Extend BaseModels to create a model for the users table.

@@ -79,2 +75,3 @@ 2 Initialize Connection: Use the super() method to pass the database connection (e.g., pool) to the BaseModels class.

```JavaScript
// suppose tabet name is uses than create file User.js write code like this and use it in controller
const { BaseModels } = require("@krishnapawar/kp-mysql-models");

@@ -93,33 +90,18 @@ const { pool } = require("./db"); // Import the pool connection

```
>In cases where the table or database connection is not automatically established or results in an error, you can manually set the table and database connection within the constructor. Here is the revised code let's take a look:
### Example Steps 2
## Example 1 for table
```JavaScript
```javascript
const { BaseModels } = require("@krishnapawar/kp-mysql-models");
const { pool } =require("./db");
class User extends BaseModels{
constructor(){
super(pool);
this._table="users";
}
class User extends BaseModels {
constructor() {
super(pool);
this._table = "users";
}
}
module.exports= User;
module.exports = User;
```
## Example 2 for database
```JavaScript
const { BaseModels } = require("@krishnapawar/kp-mysql-models");
const { pool } =require("./db");
class User extends BaseModels{
constructor(){
super();
this._connection=pool;
}
}
module.exports= User;
```
## Example 3
### Example Steps 3
>We can customize other model settings such as soft delete, hidden fields, and fields to show. Here's how you can implement this:

@@ -150,51 +132,52 @@

>You can access all methods after make User class object for Example
```JavaScript
let user = new User;
## Usage Examples
// for single data
let data = await user.first();
### Fetching Data
// for all data
let data = await user.get();
```javascript
// Get the first user
const firstUser = await User.first();
//deleting data
let data = await user.delele({
where: {
id: 585,
}
});
// Fetch all users
const users = await User.get();
let data = await user.deleleAll();
// Filtered query
const filteredUsers = await User.where({ role: "admin" }).get();
```
### CRUD Operations
let data = await user.destroy({
where: {
id: 585,
}
});
```javascript
// Insert new record
await User.create({ name: "John Doe", email: "john@example.com" });
let data = await user.delete({
where: {
id: 585,
}
});
//trucate table
let data = await user.trunCate();
// Update a record
await User.where({ id: 1 }).update({ name: "John Smith" });
//
let data = await User.findOne(13);
// Delete a record
await User.where({ id: 1 }).delete();
```
### Dynamic Pagination
```javascript
// Paginate results (10 records per page)
const page = await User.pagination({ currentPage: 2, perPage: 10 }).get();
```
## Working with Soft Deletes
>The kp mysql model library supports soft deletes. This allows you to mark records as "deleted" without actually removing them from the database.
```JavaScript
let user = new User();
### Soft Deletes
```javascript
// Soft delete a record
let data = await user.trashed({ where: { id: 585 } });
let data = await user.where("id", 585).trashed();
await User.where({ id: 1 }).trashed();
// Restore a soft-deleted record
await User.where({ id: 1 }).restore();
// Fetch only soft-deleted records
const deletedUsers = await User.onlyTrashed().get();
// Fetch only active records
const activedUsers = await User.withoutTrashed().get();
// Soft delete all records

@@ -206,69 +189,28 @@ let data = await user.trashedAll();

// Restore a soft-deleted record
let data = await user.restore({ where: { id: 585 } });
let data = await user.where("id", 585).restore();
// Soft clear trash records
let data = await user.clearTrash();
```
//for fetch soft deleted data useing onlyTrashed:true;
let data = await user.first({
onlyTrashed:true,
where: {
id: 585,
}
});
## Let's see more method's Example
```JavaScript
// Truncate the table
let data = await User.truncate();
//or you can use like this
let data = await user.where("id",585).onlyTrashed().first();
//delete record
let data = await user.deleleAll();
let data = await user.get({ onlyTrashed:true });
//delete record
let data = await user.destroy({
where: {
id: 585,
}
});
// Fetch only soft-deleted records
let data = await user.get({ onlyTrashed: true });
//or
let data = await user..onlyTrashed().get();
//find first record by id
let data = await User.findOne(13);
```
## Dynamic Pagination
With kp mysql model , you can implement dynamic pagination to control both the page number and the number of records per page. The library offers two approaches to define pagination:
***we can make dyanamic pagination with key word (pagination:1), 1 is page No. we can set page limit by (limit:10) key word 10 is 10 data per page or we can use or method `pagination({currentPage:1,perPage:20})` ***
```JavaScript
const data = await user.pagination({currentPage:1,perPage:20}).get();
//or
const data = await user.get({
limit: 10,
pagination: 1,
});
```
***The updateOrCreate method updates data if the record exists, or inserts new data if it doesn’t. Specify the fields to update or insert in elements, and conditions in where.***
#### Let's see another Example
```JavaScript
let page = req.query.page;
const data = await user.get({
limit: 10,
pagination: page,
});
```
## CRUD Operations
The following methods make data management straightforward by providing options to create, update, or conditionally insert/update records within the database.
***Use create to insert new data into the database. It automatically maps fields to the table columns.***
```JavaScript
let data = await User.create({name,email,password});
```
***The update method is designed for modifying existing records. Specify fields to update in the elements object and target records using the where clause.***
```JavaScript
const dataj = await User.update({
elements: {
first_name: "ram",
last_name: "ji",
},
where: {
id: 1223,
}
});
```
***The updateOrCreate method updates data if the record exists, or inserts new data if it doesn’t. Specify the fields to update or insert in elements, and conditions in where. ***
```JavaScript
const dataj = await User.updateOrCreate({

@@ -298,27 +240,174 @@ elements: {

#### findOneById()=> Data get by Id you can also use other condition by using obj like {name:"test", date:"12/10/2023"} or simply id
```javaScript
let data = await User.findOneById(13);
```
#### Example find() Method
```javaScript
let data = await User.find({
id:12,
name:"test",
date:"12/10/2023"
});
```
### Relationships
## Database Relations Using with() Method or with key
The with() method in kp mysql model allows you to establish and query relational data within your models, supporting relationships like `hasOne`, `belongsTo`, `hasMany`, and `connect`. This method lets you fetch related data alongside the main record in a structured way, and you can even build multi-level relationships for nested data retrieval.
`Note:-` `belongsTo` and `hasOne` give single response with single object data and other hand `hasMany` and `connect`, give array object response with multiple object data `.
```JavaScript
const data = await user.select(["id","first_name","last_name"]).with({
doctor: {
table: "appointments",
limit: 2,
select: ["id", "user_id"],
hasMany: {
user_id: "id",
},
#### The best way you can do same like create ralation in model and than call for example
```javaScript
class User extends BaseModel {
constructor() {
super(pool);
}
// Example 1: Define Relationships
business(){
return this.hasOne('businesses',{'user_id':'id'});
}
// Example 2: Define Relationships
orders(){
return this.hasMany('orders',{'business_id':'business_id'},async()=>{
return {
where:{
order_status:"pending"
}
}
});
}
// Example 3: Define Relationships
order_items(){
return this.hasMany('order_items',{'order_id':'order_id'},{
where:{
status:"pending"
}
});
}
// Example 4: Define Relationships
menu_item(){
return this.belongsTo('menu_items',{'menu_item_id':'menu_item_id'});
}
//set all relation method in init
init(){
return[
this.orders(),
this.business(),
this.menu_item(),
this.order_items()
]
}
}
export default new User;
```
## Calling in controller
#### Example 1: Retrieve Related Data
```javaScript
let data = await User.where('id',1).with('business').get();
//output
=>[
{
"id": 1,
"name": "krish",
"email": "krish@test.com",
"password": "1weqweq",
"role": "staff",
"phone": null,
"created_at": "2024-12-20T19:36:05.000Z",
"updated_at": "2024-12-20T19:36:05.000Z",
"business": {
"business_id": 1,
"user_id": 1,
"name": "The Gourmet Kitchen",
"address": "123 Flavor Street, Food City",
"phone": "123-456-7890",
"email": "contact@gourmetkitchen.com",
"created_at": "2024-11-01T16:58:28.000Z",
"updated_at": "2024-12-20T19:37:03.000Z"
}
}).with({
clinic: {
table: "appointments",
limit: 2,
select: ["id", "user_id"],
hasOne: {
doctor_id: "id",
},
}
}).where({id: 585}).first();
}
]
```
#### Example 2: Retrieve Related Data
```javaScript
let data = await User.where('id',1).with('business',{where:{"business_id": 2}}).get();
//or
//output
=>[
{
"id": 1,
"name": "krish",
"email": "krish@test.com",
"password": "1weqweq",
"role": "staff",
"phone": null,
"created_at": "2024-12-20T19:36:05.000Z",
"updated_at": "2024-12-20T19:36:05.000Z",
"business": {}
}
]
```
### Multi-Level Relationship Example
Retrieve data from multiple nested tables
```javaScript
// example 3
let data = await User.where('id',1).with({
'business':(q)=>q.setWith({
"orders":(q)=>q.setWith({
'order_items':(q)=>q.setWith('menu_item')
})
})
}).get();
// example 4 adding condition in with method
let data = await User.where('id',1).with({
'business':(q)=>q.setWith({
"orders":(q)=>q.setWith({
'order_items':(q)=>q.setWith('menu_item')
},{
where:{
"order_id": 1
}
})
})
}).get();
// example 5 adding complex raletion in easy way using with method
let data = await User.where('id',1).with({
'business':(q)=>q.setWith({
"orders":(q)=>q.setWith({
'order_items':(q)=>q.setWith('menu_item')
},{
where:{
"order_id": 1
}
})
})
}).with('menu_item',{where:{id:1}}).get();
```
***Note:-*** the key same as relation method name that we write in model
### Same thing we do and achieve by using config object
```JavaScript
const data = await user.first({

@@ -376,86 +465,4 @@ select: [

```
### Advanced Examples Using Multiple Relationships
Specify various types of relationships such as hasOne, belongsTo, and connect for flexible relational querying.
```JavaScript
{
select:['id','first_name','role_id','created_at'],
whereIsNotNull:['last_name'],
with:{
single_appointment:{
select:['id','user_id'],
table:"appointments",
hasOne:{
user_id:'id'
}
},
belongsTo_appointment:{
select:['id','user_id'],
table:"appointments",
belongsTo:{
user_id:'id'
}
},
connect_appointment:{
select:['id','user_id'],
table:"appointments",
connect:{
user_id:'id'
}
},
allAppointment:{
select:['id','user_id'],
table:"appointments",
hasMany:{
user_id:'id'
}
},
doctor: {
table: "users",
select: ["id", "user_id"],
hasOne: {
user_id: "dr_id",
},
},
}
}
```
### Multi-Level Relationship Example
Retrieve data from multiple nested tables, like users associated with appointments,
and each appointment's doctor linked to a clinic.
```javaScript
let data = await User.get({
with:{
appointment:{
select:['id','user_id'],
table:"appointments",
hasOne:{
user_id:'id'
},
with:{
doctor:{
select:['id as doctor_id','email'],
table:"users",
hasOne:{
user_id:'id'
},
with:{
clinic_data:{
table:"clinices",
hasOne:{
id:'doctor_id'
}
}
}
}
}
}
}
});
```
> `belongsTo` and `hasOne` give single response with single object data and other hand `hasMany` and `connect`, give array object response with multiple object data `.
## Advanced Joins with dbJoin() Method and dbWith() Method
### Advanced Joins with dbJoin() Method and dbWith() Method
The dbJoin() method in kp mysql model enables complex joins for enhanced querying. With options like join, innerJoin, leftJoin, and rightJoin, as well as pagination and sorting, it simplifies fetching related data across tables.

@@ -479,3 +486,4 @@

.dbJoin();
//or
//or we co using config object for example
const data = await user.dbJoin({

@@ -561,124 +569,2 @@ table: "users",

>Let's see more examples.
***first method for geting single data***
```JavaScript
const data = await user.select(["id", "first_name", "last_name"])
.latest('id')
.whereNull('last_name')
.whereNotIn('id',[1, 1221])
.where("id",1)
.limit(10)
.first();
//or you can use like this
const data = await user.first({
select: ["id", "first_name", "last_name"],
limit: 10,
latest: "id",
whereNotIn: {
id: [1, 1221],
},
whereIs: {
last_name: "NULL",
},
where:{
id:1
}
});
```
***get methods***
```JavaScript
const data = await user.select(["id", "first_name", "last_name"])
.latest('id')
.whereNull('last_name')
.whereNotIn('id',[1, 1221])
.limit(10)
.get();
//or you can use like this
const data = await user.get({
select: ["id", "first_name", "last_name"],
limit: 10,
latest: "id",
whereNotIn: {
id: [1, 1221],
},
whereIs: {
last_name: "NULL",
},
});
```
```JavaScript
let data = await user.get({
select: ["id", "firstname", "lastname"],
with: {
doctor: {
table: "appointments",
limit: 2,
select: ["id", "user_id"],
connect: {
user_id: "id",
},
},
clinic: {
table: "appointments",
limit: 2,
select: ["id", "user_id"],
connect: {
doctor_id: "id",
},
},
},
where: {
id: 585,
},
});
```
#### findOneById()=> Data get by Id you can also use other condition by using obj like {name:"test", date:"12/10/2023"} or simply id
```javaScript
let data = await User.findOneById(13);
```
#### Example find() Method
```javaScript
let data = await User.find({
id:12,
name:"test",
date:"12/10/2023"
});
```
### Some Important Models methods, we can use all methods by extends BaseModels in our Model,
* get,
* first,
* find
* findById
* findOne
* findOneById
* findOneByEmail
* dbQuery,
* trunCate,
* deleleAll,
* destroy,
* delete,
* create,
* update,
* updateOrCreate,
* save,
* dbJoin,
* dbWith,
* trasted,
* restore,
* trastedAll,
* restoreAll,
* trunCate,
* exists,
## Helper methods

@@ -789,19 +675,72 @@

```
***deleleAll method using for delete data***
```JavaScript
const dataj = await deleteAll({
table: "users",
where: {
id: 1223,
},
});
```
---
### Key Methods
## Key Methods
### Basic Query Methods
1. **`get()`**: Fetch multiple records.
2. **`first()`**: Fetch the first record.
3. **`find()`**: Retrieve records by conditions.
4. **`findOne()`**: Retrieve single records by conditions.
5. **`findOneByEmail()`**: Fetch a record by its Email.
6. **`findOneById()`**: Fetch a record by its ID.
7. **`where()`**: Apply conditions to filter results.
8. **`whereOr()`**: Apply OR conditions.
9. **`whereIn()`**: Filter records where a column value matches an array.
10. **`whereNotIn()`**: Exclude records where a column value matches an array.
11. **`whereNull()`**: Filter records where a column value is `NULL`.
12. **`whereNotNull()`**: Filter records where a column value is not `NULL`.
13. **`whereRaw()`**: Apply raw SQL conditions.
14. **`onlyTrashed()`**: Fetch only soft-deleted records.
15. **`exists()`**: Check if a record exists based on conditions.
16. **`pagination()`**: Implement dynamic pagination.
17. **`join()`**: Add basic join clauses.
18. **`leftJoin()`**: Add left join clauses.
19. **`rightJoin()`**: Add right join clauses.
20. **`innerJoin()`**: Add inner join clauses.
### CRUD and Other Methods
1. **`create()`**: Insert new records into the database.
2. **`update()`**: Update existing records.
3. **`delete()`**: Delete records based on conditions.
4. **`truncate()`**: Remove all records from a table.
5. **`updateOrCreate()`**: Update records if they exist or insert them if they don’t.
6. **`save()`**: Create or update records based on conditions.
7. **`destroy()`**: Delete records (supports soft delete).
8. **`restore()`**: Restore soft-deleted records.
9. **`trashed()`**: Soft delete a record.
10. **`trashedAll()`**: Soft delete all records.
11. **`restoreAll()`**: Restore all soft-deleted records.
12. **`clearTrash()`**: delete permanent trashed records.
### Relationship Methods
1. **`hasOne()`**: Define a one-to-one relationship.
2. **`hasMany()`**: Define a one-to-many relationship.
3. **`belongsTo()`**: Define an inverse one-to-one or many-to-one relationship.
4. **`connect()`**: Define a custom connection between tables.
---
### Basic Query Methods
## Configuration Keywords
| Keyword | Description |
|--------------|------------------------------------------|
| `table` | Specifies the database table |
| `select` | Columns to retrieve |
| `where` | Apply conditions |
| `join` | Define table relationships |
| `onlyTrashed`| Fetch only soft-deleted records |
| `pagination` | Define pagination settings |
| `hasOne` | Define a one-to-one relationship |
| `hasMany` | Define a one-to-many relationship |
| `groupBy` | Groups records based on specified columns|
| `raw` | Add raw SQL expressions |
---
### Let's More Query Methods in details
1. **`table(x)`**

@@ -843,31 +782,31 @@ - Specifies the table to query.

9. **`when(c, cb)`**
1. **`when(c, cb)`**
- Executes a callback function if a condition is met.
- **Example:** `query.when(userIsAdmin, q => q.where('role', 'admin'))`
10. **`where(c, v=false)`**
2. **`where(c, v=false)`**
- Adds a `WHERE` clause with specified conditions.
- **Example:** `query.where('status', 'active')`
11. **`whereOr(c, v=false)`**
3. **`whereOr(c, v=false)`**
- Adds an `OR WHERE` clause.
- **Example:** `query.whereOr('role', 'user')`
12. **`whereIn(column, values)`**
4. **`whereIn(column, values)`**
- Filters records where the column’s value is in a specified array.
- **Example:** `query.whereIn('id', [1, 2, 3])`
13. **`whereNotIn(column, values)`**
5. **`whereNotIn(column, values)`**
- Filters records where the column’s value is *not* in a specified array.
- **Example:** `query.whereNotIn('status', ['inactive', 'deleted'])`
14. **`whereNull(column)`**
6. **`whereNull(column)`**
- Filters records where the column’s value is `NULL`.
- **Example:** `query.whereNull('deleted_at')`
15. **`whereNotNull(column)`**
7. **`whereNotNull(column)`**
- Filters records where the column’s value is *not* `NULL`.
- **Example:** `query.whereNotNull('created_at')`
16. **`whereRaw(c)`**
8. **`whereRaw(c)`**
- Adds a raw `WHERE` clause.

@@ -880,7 +819,7 @@ - **Example:** `query.whereRaw('age > 18')`

17. **`with(c)`**
1. **`with(c)`**
- Defines relationships to load with the main query (similar to Eloquent's `with`).
- **Example:** `query.with({ posts: { ... } })`
18. **`pagination(x)`**
2. **`pagination(x)`**
- Configures pagination by setting a `currentPage` and `perPage` limit.

@@ -893,19 +832,15 @@ - **Example:** `query.pagination({ currentPage: 1, perPage: 20 })`

19. **Private Method: `#addJoin(type, table, key, value, cb)`**
- Adds a join clause (`JOIN`, `INNER JOIN`, `LEFT JOIN`, or `RIGHT JOIN`) to the query.
- Supports sub-joins through callback functions.
20. **`rightJoin(x, y, z, cb=false)`**
1. **`rightJoin(x, y, z, cb=false)`**
- Adds a `RIGHT JOIN` clause.
- **Example:** `query.rightJoin('comments', 'users.id', 'comments.user_id')`
21. **`innerJoin(x, y, z, cb=false)`**
2. **`innerJoin(x, y, z, cb=false)`**
- Adds an `INNER JOIN` clause.
- **Example:** `query.innerJoin('posts', 'users.id', 'posts.user_id')`
22. **`join(x, y, z, cb=false)`**
3. **`join(x, y, z, cb=false)`**
- Adds a `JOIN` clause.
- **Example:** `query.join('orders', 'users.id', 'orders.user_id')`
23. **`leftJoin(x, y, z, cb=false)`**
4. **`leftJoin(x, y, z, cb=false)`**
- Adds a `LEFT JOIN` clause.

@@ -922,2 +857,181 @@ - **Example:** `query.leftJoin('profile', 'users.id', 'profile.user_id')`

## ***Some Important config Key Words that can help in that methods,***
***
1. **table:**
- Represents the fundamental structure for storing data in a relational database.
2. **select:**
- Used to retrieve specific columns from a table, allowing developers to fetch only the necessary data.
3. **elements:**
- Denotes individual pieces of data within a table, referring to the distinct values or attributes stored.
4. **latest:**
- Facilitates the retrieval of the most recent records from a table based on a specified criterion, often a timestamp.
5. **limit:**
- Restricts the number of records returned in a query, helping manage the quantity of data retrieved.
6. **pagination:**
- A technique for breaking down large result sets into smaller, manageable chunks, commonly used for displaying data in paginated user interfaces .
7. **with:**
- Used in ORM frameworks to specify related data that should be retrieved along with the main query, optimizing data retrieval for relationships.
8. **connect:**
- Establishes connections between tables in an ORM framework, enabling the definition of relationships between entities.
9. **hasOne:**
- Indicates a one-to-one relationship between tables, specifying that one record in the first table is associated with exactly one record in the second table.
10. **belongsTo:**
- Denotes the inverse of a "hasOne" relationship, specifying the table that another table is associated with in a one-to-one relationship.
11. **hasMany:**
- Specifies a one-to-many relationship between tables, where one record in the first table can be associated with multiple records in the second table.
12. **join:**
- Combines data from multiple tables based on specified conditions, allowing for the retrieval of interconnected information.
13. **dbWith:**
- Similar to "with," used in ORM frameworks to specify additional data to be retrieved along with the main query, aiding in optimizing data fetching.
14. **where:**
- Filters data based on specified conditions, allowing developers to narrow down the result set to records that meet certain criteria.
15. **whereOr, whereIn, whereNotIn, whereIs, whereIsNull, whereIsNotNull, whereRaw:**
- Different variations of the "where" clause, providing flexibility in constructing precise queries with various conditions.
16. **on, onOr, onIn, onNotIn, onIs, onRaw:**
- Used in join operations to define conditions under which tables are linked, refining the result set based on specific criteria.
17. **onlyTrashed:**
- Used in the context of soft deletes, indicating that only records marked as deleted should be included in the query results.
18. **groupBy:**
- Groups query results based on specified columns, allowing for data aggregation using aggregate functions like COUNT or SUM.
19. **raw:**
- Enables the inclusion of raw SQL expressions in a query, providing flexibility for complex queries and custom database operations. Exercise caution to prevent SQL injection vulnerabilities.
20. **having:**
- Specifies conditions on aggregated data, similar to the WHERE clause but used for aggregate functions. For example, filtering groups created by GROUP BY.
## ***Here are the descriptions for the provided `where` and `on` operations with examples***
***
### Where Operations:
1. **where:-**
- Filters records where the 'id' is equal to 1223.
```JavaScript
where: {
id: 1223,
}
```
2. **whereOr:-**
- Filters records where the 'id' is equal to 1223 using the logical OR operator, allowing for multiple conditions.
```JavaScript
whereOr: {
id: 1223,
}
```
3. **whereIn:-**
- Filters records where the 'id' is either 1 or 1221, allowing for multiple values using the IN clause.
```JavaScript
whereIn: {
id: [1, 1221],
}
```
4. **whereNotIn:-**
- Filters records where the 'id' is not in the list [1, 1221], excluding records with specified values.
```JavaScript
whereNotIn: {
id: [1, 1221],
}
```
5. **whereIs:-**
- Filters records where the 'last_name' is explicitly set to NULL.
```JavaScript
whereIs: {
last_name: "NULL",
}
```
6. **whereIsNot:-**
- Filters records where the 'last_name' is not set to NULL.
```JavaScript
whereIsNot: {
last_name: "NULL",
}
```
7. **whereRaw:-**
- Allows the use of raw SQL conditions, in this case filtering records where 'name' is 'mohan' and 'age' is 30.
```JavaScript
whereRaw:"name='mohan' and age=30 "
```
### On Operations:
1. **on:-**
- Specifies a condition for joining tables based on the 'id' being equal to 1223.
```JavaScript
on: {
id: 1223,
}
```
2. **onOr:-**
- Specifies a condition for joining tables based on the 'id' being equal to 1223 using the logical OR operator.
```JavaScript
onOr: {
id: 1223,
}
```
3. **onIn:-**
- Specifies a condition for joining tables based on the 'id' being either 1 or 1221, using the IN clause.
```JavaScript
onIn: {
id: [1, 1221],
}
```
4. **onNotIn:-**
- Specifies a condition for joining tables based on the 'id' not being in the list [1, 1221], excluding certain values.
```JavaScript
onNotIn: {
id: [1, 1221],
}
```
5. **onIs:-**
- Specifies a condition for joining tables based on the 'last_name' being explicitly set to NULL.
```JavaScript
onIs: {
last_name: "NULL",
}
```
6. **onIsNot:-**
- Specifies a condition for joining tables based on the 'last_name' not being set to NULL.
```JavaScript
onIsNot: {
last_name: "NULL",
}
```
7. **onRaw:-**
- Allows the use of raw SQL conditions for joining tables, in this case specifying conditions where 'name' is 'mohan' and 'age' is 30.
```JavaScript
onRaw:"name='mohan' and age=30 "
```
## ***collect Method***

@@ -1203,180 +1317,2 @@

## ***Some Important Key Words that can help in that methods,***
***
1. **table:**
- Represents the fundamental structure for storing data in a relational database.
2. **select:**
- Used to retrieve specific columns from a table, allowing developers to fetch only the necessary data.
3. **elements:**
- Denotes individual pieces of data within a table, referring to the distinct values or attributes stored.
4. **latest:**
- Facilitates the retrieval of the most recent records from a table based on a specified criterion, often a timestamp.
5. **limit:**
- Restricts the number of records returned in a query, helping manage the quantity of data retrieved.
6. **pagination:**
- A technique for breaking down large result sets into smaller, manageable chunks, commonly used for displaying data in paginated user interfaces .
7. **with:**
- Used in ORM frameworks to specify related data that should be retrieved along with the main query, optimizing data retrieval for relationships.
8. **connect:**
- Establishes connections between tables in an ORM framework, enabling the definition of relationships between entities.
9. **hasOne:**
- Indicates a one-to-one relationship between tables, specifying that one record in the first table is associated with exactly one record in the second table.
10. **belongsTo:**
- Denotes the inverse of a "hasOne" relationship, specifying the table that another table is associated with in a one-to-one relationship.
11. **hasMany:**
- Specifies a one-to-many relationship between tables, where one record in the first table can be associated with multiple records in the second table.
12. **join:**
- Combines data from multiple tables based on specified conditions, allowing for the retrieval of interconnected information.
13. **dbWith:**
- Similar to "with," used in ORM frameworks to specify additional data to be retrieved along with the main query, aiding in optimizing data fetching.
14. **where:**
- Filters data based on specified conditions, allowing developers to narrow down the result set to records that meet certain criteria.
15. **whereOr, whereIn, whereNotIn, whereIs, whereIsNull, whereIsNotNull, whereRaw:**
- Different variations of the "where" clause, providing flexibility in constructing precise queries with various conditions.
16. **on, onOr, onIn, onNotIn, onIs, onRaw:**
- Used in join operations to define conditions under which tables are linked, refining the result set based on specific criteria.
17. **onlyTrashed:**
- Used in the context of soft deletes, indicating that only records marked as deleted should be included in the query results.
18. **groupBy:**
- Groups query results based on specified columns, allowing for data aggregation using aggregate functions like COUNT or SUM.
19. **raw:**
- Enables the inclusion of raw SQL expressions in a query, providing flexibility for complex queries and custom database operations. Exercise caution to prevent SQL injection vulnerabilities.
20. **this._having**
- Specifies conditions on aggregated data, similar to the WHERE clause but used for aggregate functions. For example, filtering groups created by GROUP BY.
## ***Here are the descriptions for the provided `where` and `on` operations with examples***
***
### Where Operations:
1. **where:-**
- Filters records where the 'id' is equal to 1223.
```JavaScript
where: {
id: 1223,
}
```
2. **whereOr:-**
- Filters records where the 'id' is equal to 1223 using the logical OR operator, allowing for multiple conditions.
```JavaScript
whereOr: {
id: 1223,
}
```
3. **whereIn:-**
- Filters records where the 'id' is either 1 or 1221, allowing for multiple values using the IN clause.
```JavaScript
whereIn: {
id: [1, 1221],
}
```
4. **whereNotIn:-**
- Filters records where the 'id' is not in the list [1, 1221], excluding records with specified values.
```JavaScript
whereNotIn: {
id: [1, 1221],
}
```
5. **whereIs:-**
- Filters records where the 'last_name' is explicitly set to NULL.
```JavaScript
whereIs: {
last_name: "NULL",
}
```
6. **whereIsNot:-**
- Filters records where the 'last_name' is not set to NULL.
```JavaScript
whereIsNot: {
last_name: "NULL",
}
```
7. **whereRaw:-**
- Allows the use of raw SQL conditions, in this case filtering records where 'name' is 'mohan' and 'age' is 30.
```JavaScript
whereRaw:"name='mohan' and age=30 "
```
### On Operations:
1. **on:-**
- Specifies a condition for joining tables based on the 'id' being equal to 1223.
```JavaScript
on: {
id: 1223,
}
```
2. **onOr:-**
- Specifies a condition for joining tables based on the 'id' being equal to 1223 using the logical OR operator.
```JavaScript
onOr: {
id: 1223,
}
```
3. **onIn:-**
- Specifies a condition for joining tables based on the 'id' being either 1 or 1221, using the IN clause.
```JavaScript
onIn: {
id: [1, 1221],
}
```
4. **onNotIn:-**
- Specifies a condition for joining tables based on the 'id' not being in the list [1, 1221], excluding certain values.
```JavaScript
onNotIn: {
id: [1, 1221],
}
```
5. **onIs:-**
- Specifies a condition for joining tables based on the 'last_name' being explicitly set to NULL.
```JavaScript
onIs: {
last_name: "NULL",
}
```
6. **onIsNot:-**
- Specifies a condition for joining tables based on the 'last_name' not being set to NULL.
```JavaScript
onIsNot: {
last_name: "NULL",
}
```
7. **onRaw:-**
- Allows the use of raw SQL conditions for joining tables, in this case specifying conditions where 'name' is 'mohan' and 'age' is 30.
```JavaScript
onRaw:"name='mohan' and age=30 "
```
## License

@@ -1386,10 +1322,6 @@

# My Social Media Profiles
## Connect with Me
[![LinkedIn](https://img.shields.io/badge/LinkedIn-%230077B5.svg?&style=flat-square&logo=LinkedIn&logoColor=white)](https://in.linkedin.com/in/krishna-pawar-6250ab180)
[![GitHub](https://img.shields.io/badge/GitHub-%23121011.svg?&style=flat-square&logo=GitHub&logoColor=white)](https://github.com/krishnapawar)
[![Instagram](https://img.shields.io/badge/Instagram-%23E4405F.svg?&style=flat-square&logo=Instagram&logoColor=white)](https://www.instagram.com/krishna_p_15)
[![Twitter](https://img.shields.io/badge/Twitter-%231DA1F2.svg?&style=flat-square&logo=Twitter&logoColor=white)](https://twitter.com/YourTwitterHandle)
[![Twitter](https://img.shields.io/badge/Twitter-%231DA1F2.svg?&style=flat-square&logo=Twitter&logoColor=white)](https://x.com/krishnapawar154)
SocketSocket SOC 2 Logo

Product

  • Package Alerts
  • Integrations
  • Docs
  • Pricing
  • FAQ
  • Roadmap
  • Changelog

About

Packages

npm

Go

Maven

PyPI

Rubygems

Stay in touch

Get open source security insights delivered straight into your inbox.

  • Terms
  • Privacy
  • Security

Made with ⚡️ by Socket Inc